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

161

--error 1146

166

162

SELECT * from t2;

167

163

DROP TABLE t1;

168

164

DROP TABLE IF EXISTS t2;

184

180

185

181

# Test default table type

186

182

187

SET SESSION storage_engine="MEMORY";

183

SET SESSION storage_engine="heap";

188

184

SELECT @@storage_engine;

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 AND TABLE_SCHEMA = SCHEMA() > 1 ORDER BY TABLE_SCHEMA, TABLE_NAME;

245

show status like "Opened_tables";

251

246

select * from t1;

252

247

drop table t1;

253

248

256

251

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

257

252

258

253

259

--error ER_WRONG_TABLE_NAME

254

--error 1103

260

255

create table `t1 `(a int);

261

--error ER_WRONG_DB_NAME

256

--error 1102

262

257

create database `db1 `;

263

--error ER_WRONG_COLUMN_NAME

258

--error 1166

264

259

create table t1(`a ` int);

265

260

266

261

268

263

# "Parser permits multiple commas without syntax error"

269

264

270

265

271

--error ER_PARSE_ERROR

266

--error 1064

272

267

create table t1 (a int,);

273

--error ER_PARSE_ERROR

268

--error 1064

274

269

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

275

--error ER_PARSE_ERROR

270

--error 1064

276

271

create table t1 (,b int);

277

272

278

273

281

276

282

277

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

283

278

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

284

--error ER_ROW_IS_REFERENCED

279

--error 1217

285

280

drop table if exists t1,t2;

286

281

drop table if exists t2,t1;

287

282

314

309

create database mysqltest;

315

310

create table mysqltest.t3 like t1;

316

311

create temporary table t3 like mysqltest.t3;

317

318

312

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

319

313

show create table t3;

320

321

314

create table t2 like t3;

322

323

315

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

324

316

show create table t2;

325

317

select * from t2;

326

327

--error ER_TABLE_EXISTS_ERROR

328

318

create table t3 like t1;

329

330

--error ER_TABLE_EXISTS_ERROR

319

--error 1050

331

320

create table t3 like mysqltest.t3;

332

333

--error ER_BAD_DB_ERROR

321

--error 1049

334

322

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

323

--error ER_NO_SUCH_TABLE

324

create table t3 like non_existing_table;

325

--error 1050

340

326

create temporary table t3 like t1;

341

327

drop table t1, t2, t3;

328

drop table t3;

342

329

drop database mysqltest;

343

330

344

331

345

332

# Test default table type

346

333

347

SET SESSION storage_engine="MEMORY";

334

SET SESSION storage_engine="heap";

348

335

SELECT @@storage_engine;

349

336

CREATE TEMPORARY TABLE t1 (a int not null);

350

337

show create table t1;

351

338

drop table t1;

352

--error ER_UNKNOWN_STORAGE_ENGINE

339

--error 1286

353

340

SET SESSION storage_engine="gemini";

354

341

SELECT @@storage_engine;

355

342

CREATE TEMPORARY TABLE t1 (a int not null);

390

377

drop table t1, t2;

391

378

392

379

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

380

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

381

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

396

382

show create table t2;

421

407

422

408

423

409

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

424

--error ER_DUPLICATED_VALUE_IN_TYPE

410

--error 1291

425

411

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

426

--error ER_DUPLICATED_VALUE_IN_TYPE

412

--error 1291

427

413

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

428

414

drop table t1;

429

415

443

429

444

430

445

431

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

446

#--error ER_WRONG_NAME_FOR_INDEX

432

#--error 1280

447

433

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

448

#--error ER_WRONG_NAME_FOR_INDEX

434

#--error 1280

449

435

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

450

436

451

437

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

456

442

#show create table t2;

457

443

458

444

#create table t3 (a int);

459

#--error ER_WRONG_NAME_FOR_INDEX

445

#--error 1280

460

446

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

461

#--error ER_WRONG_NAME_FOR_INDEX

447

#--error 1280

462

448

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

463

449

464

450

#create table t4 (`primary` int);

494

480

# an improper fix is present.

495

481

496

482

create table t1 (a int);

497

--error ER_UPDATE_TABLE_USED

483

--error 1093

498

484

create table t1 select * from t1;

499

485

## TODO: Huh? --error ER_WRONG_OBJECT

500

486

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

505

491

506

492

# Bug#10413: Invalid column name is not rejected

507

493

508

--error ER_WRONG_TABLE_NAME

494

--error 1103

509

495

create table t1(column.name int);

510

--error ER_WRONG_TABLE_NAME

496

--error 1103

511

497

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

512

--error ER_WRONG_DB_NAME

498

--error 1102

513

499

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

514

500

create table t1(t1.name int);

515

501

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

582

568

show create table t1;

583

569

drop table t1;

584

570

585

--error ER_NO_DEFAULT_FOR_FIELD

571

--error 1364

586

572

create table t1 (

587

573

a varchar(12) collate utf8_bin not null,

588

574

b int not null, primary key (a)

664

650

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

665

651

666

652

# Error during open_and_lock_tables() of tables

667

--error ER_TABLE_UNKNOWN

653

--error ER_NO_SUCH_TABLE

668

654

create table t1 select * from t2;

669

655

# Rather special error which also caught during open tables pahse

670

656

--error ER_UPDATE_TABLE_USED

715

701

create table if not exists t1 select 1;

716

702

select * from t1;

717

703

drop temporary table t1;

718

--error ER_TABLE_UNKNOWN

704

--error ER_NO_SUCH_TABLE

719

705

select * from t1;

720

706

--error ER_BAD_TABLE_ERROR

721

707

drop table t1;

1036

1022

# to be rewritten to hit the limit

1037

1023

1038

1024

# Ensure limit is really 64 keys

1039

--error ER_TOO_MANY_KEYS

1025

--error 1069

1040

1026

alter table t1 add key

1041

1027

a065_long_123456789_123456789_123456789_123456789_123456789_1234 (

1042

1028

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

1050

1036

c16 int, c17 int);

1051

1037

1052

1038

# Get error for max key parts

1053

--error ER_TOO_MANY_KEY_PARTS

1039

--error 1070

1054

1040

alter table t1 add key i1 (

1055

1041

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

1056

1042

1057

1043

# Get error for max key-name length

1058

--error ER_TOO_LONG_IDENT

1044

--error 1059

1059

1045

alter table t1 add key

1060

1046

a001_long_123456789_123456789_123456789_123456789_123456789_12345 (c1);

1061

1047

1110

1096

# precedence

1111

1097

1112

1098

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

1113

#SHOW INDEX FROM t1;

1099

SHOW INDEX FROM t1;

1114

1100

DROP TABLE t1;

1115

1101

1116

1102

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

1117

#SHOW INDEX FROM t1;

1103

SHOW INDEX FROM t1;

1118

1104

DROP TABLE t1;

1119

1105

1120

1106

1160

1146

# Test incorrect database names

1161

1147

1162

1148

1163

--error ER_WRONG_DB_NAME

1149

--error 1102

1164

1150

CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1165

--error ER_WRONG_DB_NAME

1151

--error 1102

1166

1152

DROP DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1167

1153

1168

1154

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

1169

# --error ER_BAD_DB_ERROR

1155

# --error 1049

1170

1156

# RENAME DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa TO a;

1171

# --error ER_WRONG_DB_NAME

1157

# --error 1102

1172

1158

# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1173

1159

# create database mysqltest;

1174

# --error ER_WRONG_DB_NAME

1160

# --error 1102

1175

1161

# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1176

1162

# drop database mysqltest;

1177

1163

1178

--error ER_WRONG_DB_NAME

1164

--error 1102

1179

1165

USE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1180

#--error ER_WRONG_DB_NAME

1166

--error 1102

1181

1167

SHOW CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1182

1168

1183

1184

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

1185

1169

1170

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

1171

1186

1172

1187

1173

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

1188

1174

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

1189

1175

select database();

1190

1176

use test;

1191

1177

1192

select SCHEMA_NAME from data_dictionary.schemas

1178

select SCHEMA_NAME from information_schema.schemata

1193

1179

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

1194

1180

1195

1181

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

1203

1189

# database, table, field, key

1204

1190

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

1205

1191

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

1192

select TABLE_NAME from information_schema.tables where

1193

table_schema='test';

1194

1195

select COLUMN_NAME from information_schema.columns where

1196

table_schema='test';

1197

1198

select INDEX_NAME from information_schema.statistics where

1213

1199

table_schema='test';

1214

1200

1215

1201

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

1217

1203

1218

1204

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

1219

1205

1220

1206

1221

1207

1222

1208

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

1223

1209

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;

1210

create table t1 like information_schema.processlist;

1227

1211

show create table t1;

1228

1212

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;

1213

create temporary table t1 like information_schema.processlist;

1232

1214

show create table t1;

1233

1215

drop table t1;

1234

1216

1256

1238

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

1257

1239

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

1258

1240

c6 VARCHAR(255))

1259

COLLATE=utf8_bin;

1241

COLLATE utf8_bin;

1260

1242

1261

1243

--echo

1262

1244

1323

1305

--echo

1324

1306

1325

1307

CREATE TABLE t3(c1 DATETIME NOT NULL);

1326

--error ER_INVALID_DATETIME_VALUE # Bad datetime

1308

--error 1686 # Bad datetime

1327

1309

INSERT INTO t3 VALUES (0);

1328

1310

1329

1311

--echo

Older »