~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

#--error 1067

#create table t1 (a int default 1000);

--error ER_INVALID_DEFAULT

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

100

--error 1103

105

101

drop table if exists ``;

106

--error ER_WRONG_COLUMN_NAME

102

--error 1166

107

103

create table t1 (`` int);

108

--error ER_WRONG_NAME_FOR_INDEX

104

--error 1280

109

105

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

110

106

111

107

144

140

describe t1;

145

141

describe t2;

146

142

drop table if exists t2;

147

--error ER_DUP_FIELDNAME

143

--error 1060

148

144

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

149

145

drop table if exists t2;

150

--error ER_DUP_FIELDNAME

146

--error 1060

151

147

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

152

148

drop table if exists t2;

153

--error ER_DUP_FIELDNAME

149

--error 1060

154

150

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

155

151

drop table if exists t1,t2;

156

152

162

158

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

163

159

--error ER_DUP_ENTRY

164

160

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

165

--error ER_NO_SUCH_TABLE

161

--error 1146

166

162

SELECT * from t2;

167

163

DROP TABLE t1;

168

164

DROP TABLE IF EXISTS t2;

189

185

CREATE TEMPORARY TABLE t1 (a int not null);

190

186

show create table t1;

191

187

drop table t1;

192

--error ER_UNKNOWN_STORAGE_ENGINE

188

--error 1286

193

189

SET SESSION storage_engine="gemini";

194

190

SELECT @@storage_engine;

195

191

CREATE TEMPORARY TABLE t1 (a int not null);

203

199

204

200

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

205

201

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

206

--error ER_BAD_NULL_ERROR

202

--error 1048

207

203

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

208

--error ER_BAD_NULL_ERROR

204

--error 1048

209

205

insert into t1 values (NULL, 3);

210

--error ER_BAD_NULL_ERROR

206

--error 1048

211

207

insert into t1 values (NULL, NULL);

212

208

drop table t1;

213

209

223

219

224

220

225

221

create table t1 select 1,2,3;

226

--error ER_NO_DEFAULT_FOR_FIELD

222

--error 1364

227

223

create table if not exists t1 select 1,2;

228

--error ER_WRONG_VALUE_COUNT_ON_ROW

224

--error 1136

229

225

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

230

--error ER_NO_DEFAULT_FOR_FIELD

226

--error 1364

231

227

create table if not exists t1 select 1;

232

228

select * from t1;

233

229

drop table t1;

246

242

--error ER_DUP_ENTRY

247

243

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

248

244

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;

245

--replace_column 2 #

246

show status like "Opened_tables";

251

247

select * from t1;

252

248

drop table t1;

253

249

256

252

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

257

253

258

254

259

--error ER_WRONG_TABLE_NAME

255

--error 1103

260

256

create table `t1 `(a int);

261

--error ER_WRONG_DB_NAME

257

--error 1102

262

258

create database `db1 `;

263

--error ER_WRONG_COLUMN_NAME

259

--error 1166

264

260

create table t1(`a ` int);

265

261

266

262

268

264

# "Parser permits multiple commas without syntax error"

269

265

270

266

271

--error ER_PARSE_ERROR

267

--error 1064

272

268

create table t1 (a int,);

273

--error ER_PARSE_ERROR

269

--error 1064

274

270

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

275

--error ER_PARSE_ERROR

271

--error 1064

276

272

create table t1 (,b int);

277

273

278

274

281

277

282

278

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

283

279

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

284

--error ER_ROW_IS_REFERENCED

280

--error 1217

285

281

drop table if exists t1,t2;

286

282

drop table if exists t2,t1;

287

283

320

316

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

321

317

show create table t2;

322

318

select * from t2;

323

--error ER_TABLE_EXISTS_ERROR

319

--error 1050

324

320

create table t3 like t1;

325

--error ER_TABLE_EXISTS_ERROR

321

--error 1050

326

322

create table t3 like mysqltest.t3;

327

--error ER_BAD_DB_ERROR

323

--error 1049

328

324

create table non_existing_database.t1 like t1;

329

325

--error ER_NO_SUCH_TABLE

330

326

create table t3 like non_existing_table;

331

--error ER_TABLE_EXISTS_ERROR

327

--error 1050

332

328

create temporary table t3 like t1;

333

329

drop table t1, t2, t3;

334

330

drop database mysqltest;

341

337

CREATE TEMPORARY TABLE t1 (a int not null);

342

338

show create table t1;

343

339

drop table t1;

344

--error ER_UNKNOWN_STORAGE_ENGINE

340

--error 1286

345

341

SET SESSION storage_engine="gemini";

346

342

SELECT @@storage_engine;

347

343

CREATE TEMPORARY TABLE t1 (a int not null);

382

378

drop table t1, t2;

383

379

384

380

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

381

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

382

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

388

383

show create table t2;

413

408

414

409

415

410

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

416

--error ER_DUPLICATED_VALUE_IN_TYPE

411

--error 1291

417

412

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

418

--error ER_DUPLICATED_VALUE_IN_TYPE

413

--error 1291

419

414

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

420

415

drop table t1;

421

416

435

430

436

431

437

432

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

438

#--error ER_WRONG_NAME_FOR_INDEX

433

#--error 1280

439

434

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

440

#--error ER_WRONG_NAME_FOR_INDEX

435

#--error 1280

441

436

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

442

437

443

438

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

448

443

#show create table t2;

449

444

450

445

#create table t3 (a int);

451

#--error ER_WRONG_NAME_FOR_INDEX

446

#--error 1280

452

447

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

453

#--error ER_WRONG_NAME_FOR_INDEX

448

#--error 1280

454

449

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

455

450

456

451

#create table t4 (`primary` int);

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

486

## TODO: Huh? --error ER_WRONG_OBJECT

492

487

#create table t2 union = (t1) select * from 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);

574

569

show create table t1;

575

570

drop table t1;

576

571

577

--error ER_NO_DEFAULT_FOR_FIELD

572

--error 1364

578

573

create table t1 (

579

574

a varchar(12) collate utf8_bin not null,

580

575

b int not null, primary key (a)

1028

1023

# to be rewritten to hit the limit

1029

1024

1030

1025

# Ensure limit is really 64 keys

1031

--error ER_TOO_MANY_KEYS

1026

--error 1069

1032

1027

alter table t1 add key

1033

1028

a065_long_123456789_123456789_123456789_123456789_123456789_1234 (

1034

1029

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

1042

1037

c16 int, c17 int);

1043

1038

1044

1039

# Get error for max key parts

1045

--error ER_TOO_MANY_KEY_PARTS

1040

--error 1070

1046

1041

alter table t1 add key i1 (

1047

1042

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

1048

1043

1049

1044

# Get error for max key-name length

1050

--error ER_TOO_LONG_IDENT

1045

--error 1059

1051

1046

alter table t1 add key

1052

1047

a001_long_123456789_123456789_123456789_123456789_123456789_12345 (c1);

1053

1048

1152

1147

# Test incorrect database names

1153

1148

1154

1149

1155

--error ER_WRONG_DB_NAME

1150

--error 1102

1156

1151

CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1157

--error ER_WRONG_DB_NAME

1152

--error 1102

1158

1153

DROP DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1159

1154

1160

1155

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

1161

# --error ER_BAD_DB_ERROR

1156

# --error 1049

1162

1157

# RENAME DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa TO a;

1163

# --error ER_WRONG_DB_NAME

1158

# --error 1102

1164

1159

# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1165

1160

# create database mysqltest;

1166

# --error ER_WRONG_DB_NAME

1161

# --error 1102

1167

1162

# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1168

1163

# drop database mysqltest;

1169

1164

1170

--error ER_WRONG_DB_NAME

1165

--error 1102

1171

1166

USE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1172

#--error ER_WRONG_DB_NAME

1167

--error 1102

1173

1168

SHOW CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1174

1169

1175

1170

1213

1208

1214

1209

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

1215

1210

1216

--error ER_CANT_CREATE_TABLE

1211

--error 1005

1217

1212

create table t1 like data_dictionary.processlist;

1218

1213

create table t1 like data_dictionary.processlist engine=innodb;

1219

1214

show create table t1;

1220

1215

drop table t1;

1221

--error ER_CANT_CREATE_TABLE

1216

--error 1005

1222

1217

create temporary table t1 like data_dictionary.processlist;

1223

1218

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

1224

1219

show create table t1;

1248

1243

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

1249

1244

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

1250

1245

c6 VARCHAR(255))

1251

COLLATE=utf8_bin;

1246

COLLATE utf8_bin;

1252

1247

1253

1248

--echo

1254

1249

1315

1310

--echo

1316

1311

1317

1312

CREATE TABLE t3(c1 DATETIME NOT NULL);

1318

--error ER_INVALID_DATETIME_VALUE # Bad datetime

1313

--error 1686 # Bad datetime

1319

1314

INSERT INTO t3 VALUES (0);

1320

1315

1321

1316

--echo

Older »