~drizzle-trunk/drizzle/development

load data infile 'DRIZZLETEST_VARDIR/std_data_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (a, b) set c=concat(a,"+",b,"+",@c,"+",b,"+",if(c is null,"NIL",c));

189

load data infile '../std_data_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (a, b) set c=concat(a,"+",b,"+",@c,"+",b,"+",if(c is null,"NIL",c));

136

190

select * from t1;

137

191

a b c

138

192

1 2 1+2+123+2+NIL

139

193

3 4 3+4+123+4+NIL

140

194

5 6 5+6+123+6+NIL

141

load data infile 'DRIZZLETEST_VARDIR/std_data_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (a, @b);

195

load data infile '../std_data_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (a, @b);

142

196

ERROR HY000: Can't load value from file with fixed size rows to variable

143

197

create table t2 (num int primary key, str varchar(10));

144

198

insert into t2 values (10,'Ten'), (15,'Fifteen');

145

199

truncate table t1;

146

load data infile 'DRIZZLETEST_VARDIR/std_data_ln/rpl_loaddata.dat' into table t1 (@dummy,@n) set a= @n, c= (select str from t2 where num=@n);

200

load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 (@dummy,@n) set a= @n, c= (select str from t2 where num=@n);

147

201

select * from t1;

148

202

a b c

149

203

10 NULL Ten

150

15 NULL Ten

204

15 NULL Fifteen

151

205

show variables like "secure_file_pri%";

152

206

Variable_name Value

153

secure_file_priv DRIZZLETEST_VARDIR

207

secure_file_priv MYSQLTEST_VARDIR/

154

208

select @@secure_file_priv;

155

209

@@secure_file_priv

156

DRIZZLETEST_VARDIR

210

MYSQLTEST_VARDIR/

157

211

set @@secure_file_priv= 0;

158

212

ERROR HY000: Variable 'secure_file_priv' is a read only variable

159

213

truncate table t1;

160

load data infile 'DRIZZLE_TEST_DIR/t/loaddata.test' into table t1;

161

ERROR HY000: The Drizzle server is running with the --secure-file-priv option so it cannot execute this statement

214

load data infile 'MYSQL_TEST_DIR/t/loaddata.test' into table t1;

215

ERROR HY000: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

162

216

select * from t1;

163

217

a b c

164

select load_file("DRIZZLE_TEST_DIR/t/loaddata.test");

165

ERROR HY000: The Drizzle server is running with the --secure-file-priv option so it cannot execute this statement

218

select load_file("MYSQL_TEST_DIR/t/loaddata.test");

219

load_file("MYSQL_TEST_DIR/t/loaddata.test")

220

NULL

166

221

drop table t1, t2;

222

create table t1(f1 int);

223

insert into t1 values(1),(null);

224

create table t2(f2 int auto_increment primary key);

225

select * from t2;

226

227

228

229

SET @@SQL_MODE=@OLD_SQL_MODE;

230

drop table t1,t2;

167

231

create table t1(f1 int, f2 timestamp not null default current_timestamp);

168

232

create table t2(f1 int);

169

233

insert into t2 values(1),(2);

170

ERROR 01000: Row 1 doesn't contain data for all columns

171

select f1 from t1 where f2 IS NOT NULL order by f1;

234

Warnings:

235

Warning 1261 Row 1 doesn't contain data for all columns

236

Warning 1261 Row 2 doesn't contain data for all columns

237

select f1 from t1 where f2 <> '0000-00-00 00:00:00' order by f1;

172

238

239

240

173

241

delete from t1;

174

ERROR 01000: Row 1 doesn't contain data for all columns

175

select f1 from t1 where f2 IS NOT NULL order by f1;

242

Warnings:

243

Warning 1261 Row 1 doesn't contain data for all columns

244

Warning 1261 Row 2 doesn't contain data for all columns

245

select f1 from t1 where f2 <> '0000-00-00 00:00:00' order by f1;

176

246

247

248

177

249

drop table t1,t2;

178

250

CREATE TABLE t1 (c1 INT, c2 TIMESTAMP, c3 REAL, c4 DOUBLE);

179

251

INSERT INTO t1 (c1, c2, c3, c4) VALUES (10, '1970-02-01 01:02:03', 1.1E-100, 1.1E+100);

180

252

SELECT * FROM t1;

181

253

c1 c2 c3 c4

182

254

10 1970-02-01 01:02:03 1.1e-100 1.1e100

183

SELECT * INTO OUTFILE 'DRIZZLETEST_VARDIR/tmp/t1' FIELDS ENCLOSED BY '-' FROM t1;

255

SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1' FIELDS ENCLOSED BY '-' FROM t1;

256

-10- -1970\-02\-01 01:02:03- -1.1e\-100- -1.1e100-

184

257

EOF

185

258

TRUNCATE t1;

186

LOAD DATA INFILE 'DRIZZLETEST_VARDIR/tmp/t1' INTO TABLE t1 FIELDS ENCLOSED BY '-';

259

LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1' INTO TABLE t1 FIELDS ENCLOSED BY '-';

187

260

SELECT * FROM t1;

188

261

c1 c2 c3 c4

189

262

10 1970-02-01 01:02:03 1.1e-100 1.1e100

190

263

DROP TABLE t1;

264

265

# --

266

# -- Bug#35469: server crash with LOAD DATA INFILE to a VIEW.

267

# --

268

269

DROP TABLE IF EXISTS t1;

270

DROP VIEW IF EXISTS v1;

271

DROP VIEW IF EXISTS v2;

272

DROP VIEW IF EXISTS v3;

273

274

CREATE TABLE t1(c1 INT, c2 VARCHAR(255));

275

276

CREATE VIEW v1 AS SELECT * FROM t1;

277

CREATE VIEW v2 AS SELECT 1 + 2 AS c0, c1, c2 FROM t1;

278

CREATE VIEW v3 AS SELECT 1 AS d1, 2 AS d2;

279

280

LOAD DATA INFILE '../std_data_ln/bug35469.dat' INTO TABLE v1

281

FIELDS ESCAPED BY '\\'

282

TERMINATED BY ','

283

ENCLOSED BY '"'

284

LINES TERMINATED BY '\n' (c1, c2);

285

286

SELECT * FROM t1;

287

c1 c2

288

1 "string1"

289

2 "string2"

290

3 "string3"

291

292

SELECT * FROM v1;

293

c1 c2

294

1 "string1"

295

2 "string2"

296

3 "string3"

297

298

DELETE FROM t1;

299

300

LOAD DATA INFILE '../std_data_ln/bug35469.dat' INTO TABLE v2

301

FIELDS ESCAPED BY '\\'

302

TERMINATED BY ','

303

ENCLOSED BY '"'

304

LINES TERMINATED BY '\n' (c1, c2);

305

306

SELECT * FROM t1;

307

c1 c2

308

1 "string1"

309

2 "string2"

310

3 "string3"

311

312

SELECT * FROM v2;

313

c0 c1 c2

314

3 1 "string1"

315

3 2 "string2"

316

3 3 "string3"

317

318

DELETE FROM t1;

319

320

LOAD DATA INFILE '../std_data_ln/bug35469.dat' INTO TABLE v2

321

FIELDS ESCAPED BY '\\'

322

TERMINATED BY ','

323

ENCLOSED BY '"'

324

LINES TERMINATED BY '\n' (c0, c2);

325

ERROR HY000: Invalid column reference (v2.c0) in LOAD DATA

326

327

LOAD DATA INFILE '../std_data_ln/bug35469.dat' INTO TABLE v3

328

FIELDS ESCAPED BY '\\'

329

TERMINATED BY ','

330

ENCLOSED BY '"'

331

LINES TERMINATED BY '\n' (d1, d2);

332

ERROR HY000: The target table v3 of the LOAD is not updatable

333

334

DROP TABLE t1;

335

DROP VIEW v1;

336

DROP VIEW v2;

337

DROP VIEW v3;

338

339

# -- End of Bug#35469.

340

CREATE TABLE t1 (a int);

341

INSERT INTO t1 VALUES (1);

342

SET NAMES latin1;

343

SET character_set_filesystem=filename;

344

select @@character_set_filesystem;

345

@@character_set_filesystem

346

filename

347

SELECT * INTO OUTFILE 't-1' FROM t1;

348

DELETE FROM t1;

349

LOAD DATA INFILE 't-1' INTO TABLE t1;

350

SELECT * FROM t1;

351

352

353

DELETE FROM t1;

354

SET character_set_filesystem=latin1;

355

select @@character_set_filesystem;

356

@@character_set_filesystem

357

latin1

358

LOAD DATA INFILE 't@002d1' INTO TABLE t1;

359

SELECT * FROM t1;

360

361

362

DROP TABLE t1;

363

SET character_set_filesystem=default;

364

select @@character_set_filesystem;

365

@@character_set_filesystem

366

binary

Older »