~drizzle-trunk/drizzle/development

create table `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` (aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa int);

--error 1059

--error ER_TOO_LONG_IDENT

create table a (`aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` int);

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

--error 1067

--error ER_INVALID_DEFAULT

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

--error 1294

--error ER_INVALID_ON_UPDATE

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

--error 1067

--error ER_INVALID_DEFAULT

create table t1 (a int default 100 auto_increment);

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

#--error 1067

#--error ER_INVALID_DEFAULT

#create table t1 (a int default 1000);

--error 1067

--error ER_INVALID_DEFAULT

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

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

insert into t1 values();

select * from t1;

--error 1067

--error ER_INVALID_DEFAULT

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

drop table t1;

drop table mysqltest.test2$;

100

drop database mysqltest;

101

102

--error 1103

102

--error ER_WRONG_TABLE_NAME

103

create table `` (a int);

104

--error 1103

104

--error ER_WRONG_TABLE_NAME

105

drop table if exists ``;

106

--error 1166

106

--error ER_WRONG_COLUMN_NAME

107

create table t1 (`` int);

108

--error 1280

108

--error ER_WRONG_NAME_FOR_INDEX

109

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

110

111

144

describe t1;

145

describe t2;

146

drop table if exists t2;

147

--error 1060

147

--error ER_DUP_FIELDNAME

148

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

149

drop table if exists t2;

150

--error 1060

150

--error ER_DUP_FIELDNAME

151

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

152

drop table if exists t2;

153

--error 1060

153

--error ER_DUP_FIELDNAME

154

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

155

drop table if exists t1,t2;

156

162

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

163

--error ER_DUP_ENTRY

164

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

165

--error 1146

165

--error ER_NO_SUCH_TABLE

166

SELECT * from t2;

167

DROP TABLE t1;

168

DROP TABLE IF EXISTS t2;

189

CREATE TEMPORARY TABLE t1 (a int not null);

190

show create table t1;

191

drop table t1;

192

--error 1286

192

--error ER_UNKNOWN_STORAGE_ENGINE

193

SET SESSION storage_engine="gemini";

194

SELECT @@storage_engine;

195

CREATE TEMPORARY TABLE t1 (a int not null);

203

204

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

205

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

206

--error 1048

206

--error ER_BAD_NULL_ERROR

207

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

208

--error 1048

208

--error ER_BAD_NULL_ERROR

209

insert into t1 values (NULL, 3);

210

--error 1048

210

--error ER_BAD_NULL_ERROR

211

insert into t1 values (NULL, NULL);

212

drop table t1;

213

223

224

225

create table t1 select 1,2,3;

226

--error 1364

226

--error ER_NO_DEFAULT_FOR_FIELD

227

create table if not exists t1 select 1,2;

228

--error 1136

228

--error ER_WRONG_VALUE_COUNT_ON_ROW

229

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

230

--error 1364

230

--error ER_NO_DEFAULT_FOR_FIELD

231

create table if not exists t1 select 1;

232

select * from t1;

233

drop table t1;

256

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

257

258

259

--error 1103

259

--error ER_WRONG_TABLE_NAME

260

create table `t1 `(a int);

261

--error 1102

261

--error ER_WRONG_DB_NAME

262

create database `db1 `;

263

--error 1166

263

--error ER_WRONG_COLUMN_NAME

264

create table t1(`a ` int);

265

266

268

# "Parser permits multiple commas without syntax error"

269

270

271

--error 1064

271

--error ER_PARSE_ERROR

272

create table t1 (a int,);

273

--error 1064

273

--error ER_PARSE_ERROR

274

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

275

--error 1064

275

--error ER_PARSE_ERROR

276

create table t1 (,b int);

277

278

281

282

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

283

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

284

--error 1217

284

--error ER_ROW_IS_REFERENCED

285

drop table if exists t1,t2;

286

drop table if exists t2,t1;

287

320

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

321

show create table t2;

322

select * from t2;

323

--error 1050

323

--error ER_TABLE_EXISTS_ERROR

324

create table t3 like t1;

325

--error 1050

325

--error ER_TABLE_EXISTS_ERROR

326

create table t3 like mysqltest.t3;

327

--error 1049

327

--error ER_BAD_DB_ERROR

328

create table non_existing_database.t1 like t1;

329

--error ER_NO_SUCH_TABLE

330

create table t3 like non_existing_table;

331

--error 1050

331

--error ER_TABLE_EXISTS_ERROR

332

create temporary table t3 like t1;

333

drop table t1, t2, t3;

334

drop database mysqltest;

341

CREATE TEMPORARY TABLE t1 (a int not null);

342

show create table t1;

343

drop table t1;

344

--error 1286

344

--error ER_UNKNOWN_STORAGE_ENGINE

345

SET SESSION storage_engine="gemini";

346

SELECT @@storage_engine;

347

CREATE TEMPORARY TABLE t1 (a int not null);

412

413

414

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

415

--error 1291

415

--error ER_DUPLICATED_VALUE_IN_TYPE

416

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

417

--error 1291

417

--error ER_DUPLICATED_VALUE_IN_TYPE

418

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

419

drop table t1;

420

434

435

436

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

437

#--error 1280

437

#--error ER_WRONG_NAME_FOR_INDEX

438

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

439

#--error 1280

439

#--error ER_WRONG_NAME_FOR_INDEX

440

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

441

442

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

447

#show create table t2;

448

449

#create table t3 (a int);

450

#--error 1280

450

#--error ER_WRONG_NAME_FOR_INDEX

451

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

452

#--error 1280

452

#--error ER_WRONG_NAME_FOR_INDEX

453

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

454

455

#create table t4 (`primary` int);

485

# an improper fix is present.

486

487

create table t1 (a int);

488

--error 1093

488

--error ER_UPDATE_TABLE_USED

489

create table t1 select * from t1;

490

## TODO: Huh? --error ER_WRONG_OBJECT

491

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

496

497

# Bug#10413: Invalid column name is not rejected

498

499

--error 1103

499

--error ER_WRONG_TABLE_NAME

500

create table t1(column.name int);

501

--error 1103

501

--error ER_WRONG_TABLE_NAME

502

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

503

--error 1102

503

--error ER_WRONG_DB_NAME

504

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

505

create table t1(t1.name int);

506

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

573

show create table t1;

574

drop table t1;

575

576

--error 1364

576

--error ER_NO_DEFAULT_FOR_FIELD

577

create table t1 (

578

a varchar(12) collate utf8_bin not null,

579

b int not null, primary key (a)

1027

# to be rewritten to hit the limit

1028

1029

# Ensure limit is really 64 keys

1030

--error 1069

1030

--error ER_TOO_MANY_KEYS

1031

alter table t1 add key

1032

a065_long_123456789_123456789_123456789_123456789_123456789_1234 (

1033

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

1041

c16 int, c17 int);

1042

1043

# Get error for max key parts

1044

--error 1070

1044

--error ER_TOO_MANY_KEY_PARTS

1045

alter table t1 add key i1 (

1046

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

1047

1048

# Get error for max key-name length

1049

--error 1059

1049

--error ER_TOO_LONG_IDENT

1050

alter table t1 add key

1051

a001_long_123456789_123456789_123456789_123456789_123456789_12345 (c1);

1052

1151

# Test incorrect database names

1152

1153

1154

--error 1102

1154

--error ER_WRONG_DB_NAME

1155

CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1156

--error 1102

1156

--error ER_WRONG_DB_NAME

1157

DROP DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1158

1159

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

1160

# --error 1049

1160

# --error ER_BAD_DB_ERROR

1161

# RENAME DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa TO a;

1162

# --error 1102

1162

# --error ER_WRONG_DB_NAME

1163

# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1164

# create database mysqltest;

1165

# --error 1102

1165

# --error ER_WRONG_DB_NAME

1166

# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1167

# drop database mysqltest;

1168

1169

--error 1102

1169

--error ER_WRONG_DB_NAME

1170

USE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1171

--error 1102

1171

--error ER_WRONG_DB_NAME

1172

SHOW CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1173

1174

1212

1213

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

1214

1215

--error 1005

1215

--error ER_CANT_CREATE_TABLE

1216

create table t1 like data_dictionary.processlist;

1217

create table t1 like data_dictionary.processlist engine=innodb;

1218

show create table t1;

1219

drop table t1;

1220

--error 1005

1220

--error ER_CANT_CREATE_TABLE

1221

create temporary table t1 like data_dictionary.processlist;

1222

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

1223

show create table t1;

1314

--echo

1315

1316

CREATE TABLE t3(c1 DATETIME NOT NULL);

1317

--error 1686 # Bad datetime

1317

--error ER_INVALID_DATETIME_VALUE # Bad datetime

1318

INSERT INTO t3 VALUES (0);

1319

1320

--echo

Older »