~drizzle-trunk/drizzle/development

1 by brian
clean slate
1
#
2
# Some simple test of load data
3
#
4
5
--disable_warnings
6
drop table if exists t1, t2;
7
--enable_warnings
8
9
create table t1 (a date, b date, c date not null, d date);
10
load data infile '../std_data_ln/loaddata1.dat' into table t1 fields terminated by ',';
11
load data infile '../std_data_ln/loaddata1.dat' into table t1 fields terminated by ',' IGNORE 2 LINES;
12
SELECT * from t1;
13
truncate table t1;
14
15
load data infile '../std_data_ln/loaddata1.dat' into table t1 fields terminated by ',' LINES STARTING BY ',' (b,c,d);
16
SELECT * from t1;
17
drop table t1;
18
19
create table t1 (a text, b text);
20
load data infile '../std_data_ln/loaddata2.dat' into table t1 fields terminated by ',' enclosed by '''';
21
select concat('|',a,'|'), concat('|',b,'|') from t1;
22
drop table t1;
23
24
create table t1 (a int, b char(10));
25
load data infile '../std_data_ln/loaddata3.dat' into table t1 fields terminated by '' enclosed by '' ignore 1 lines;
26
select * from t1;
27
truncate table t1;
28
load data infile '../std_data_ln/loaddata4.dat' into table t1 fields terminated by '' enclosed by '' lines terminated by '' ignore 1 lines;
29
30
# The empty line last comes from the end line field in the file
31
select * from t1;
32
drop table t1;
33
34
#
35
# Bug #12053 LOAD DATA INFILE ignores NO_AUTO_VALUE_ON_ZERO setting
36
#
37
SET @OLD_SQL_MODE=@@SQL_MODE, @@SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
38
create table t1(id integer not null auto_increment primary key);
39
insert into t1 values(0);
40
disable_query_log;
41
eval SELECT * INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/t1' from t1;
42
delete from t1;
43
eval load data infile '$MYSQLTEST_VARDIR/tmp/t1' into table t1;
44
enable_query_log;
45
select * from t1;
46
remove_file $MYSQLTEST_VARDIR/tmp/t1;
47
48
disable_query_log;
49
eval SELECT * INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/t1'
50
FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\r\n'
51
FROM t1;
52
delete from t1;
53
eval load data infile '$MYSQLTEST_VARDIR/tmp/t1' into table t1
54
FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\r\n';
55
enable_query_log;
56
select * from t1;
57
remove_file $MYSQLTEST_VARDIR/tmp/t1;
58
SET @@SQL_MODE=@OLD_SQL_MODE;
59
drop table t1;
60
61
#
62
# Bug #11203: LOAD DATA does not accept same characters for ESCAPED and
63
# ENCLOSED
64
#
65
create table t1 (a varchar(20), b varchar(20));
66
load data infile '../std_data_ln/loaddata_dq.dat' into table t1 fields terminated by ',' enclosed by '"' escaped by '"' (a,b);
67
select * from t1;
68
drop table t1;
69
70
#
71
# Bug #29294 SELECT INTO OUTFILE/LOAD DATA INFILE with special
72
# characters in the FIELDS ENCLOSED BY clause
73
#
74
75
CREATE TABLE t1 (
76
  id INT AUTO_INCREMENT PRIMARY KEY,
77
  c1 VARCHAR(255)
78
);
79
80
CREATE TABLE t2 (
81
  id INT,
82
  c2 VARCHAR(255)
83
);
84
85
INSERT INTO t1 (c1) VALUES
86
  ('r'),   ('rr'),   ('rrr'),   ('rrrr'),
87
  ('.r'),  ('.rr'),  ('.rrr'),  ('.rrrr'),
88
  ('r.'),  ('rr.'),  ('rrr.'),  ('rrrr.'),
89
  ('.r.'), ('.rr.'), ('.rrr.'), ('.rrrr.');
90
SELECT * FROM t1;
91
92
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
93
eval SELECT * INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/t1' FIELDS ENCLOSED BY 'r' FROM t1;
94
cat_file $MYSQLTEST_VARDIR/tmp/t1;
95
96
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
97
eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/t1' INTO TABLE t2 FIELDS ENCLOSED BY 'r';
98
SELECT t1.id, c1, c2 FROM t1 LEFT  JOIN t2 ON t1.id=t2.id WHERE c1 != c2;
99
SELECT t1.id, c1, c2 FROM t1 RIGHT JOIN t2 ON t1.id=t2.id WHERE c1 != c2;
100
101
remove_file $MYSQLTEST_VARDIR/tmp/t1;
102
DROP TABLE t1,t2;
103
104
# End of 4.1 tests
105
106
#
107
# Let us test extended LOAD DATA features
108
#
109
create table t1 (a int default 100, b int, c varchar(60));
110
# we can do something like this
111
load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 (a, @b) set b=@b+10, c=concat("b=",@b);
112
select * from t1;
113
truncate table t1;
114
# we can use filled fields in expressions 
115
# we also assigning NULL value to field with non-NULL default here
116
load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 (a, @b) set c= if(a is null,"oops",a);
117
select * from t1;
118
truncate table t1;
119
# we even can use variables in set clause, and missed columns will be set
120
# with default values
121
set @c:=123;
122
load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 (@a, b) set c= if(@a is null,@c,b);
123
select * from t1;
124
# let us test side-effect of such load
125
load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 (@a, @b);
126
select * from t1;
127
select @a, @b;
128
truncate table t1;
129
# Reading of all columns with set
130
load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 set c=b;
131
select * from t1;
132
truncate table t1;
133
# now going to test fixed field-row file format
134
load data infile '../std_data_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (a, b) set c="Wow";
135
select * from t1;
136
truncate table t1;
137
# this also should work
138
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));
139
select * from t1;
140
# and this should bark
141
--error 1409 
142
load data infile '../std_data_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (a, @b);
143
144
# Now let us test LOAD DATA with subselect
145
create table t2 (num int primary key, str varchar(10));
146
insert into t2 values (10,'Ten'), (15,'Fifteen');
147
truncate table t1;
148
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);
149
select * from t1;
150
151
#
152
# Bug#18628 mysql-test-run: security problem
153
#
154
# It should not be possible to load from a file outside of vardir
155
156
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
157
show variables like "secure_file_pri%";
158
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
159
select @@secure_file_priv;
160
--error 1238
161
set @@secure_file_priv= 0;
162
163
# Test "load data"
164
truncate table t1;
319.1.1 by Grant Limberg
renamed all instances of MYSQL_ to DRIZZLE_
165
--replace_result $DRIZZLE_TEST_DIR DRIZZLE_TEST_DIR
1 by brian
clean slate
166
--error 1290
319.1.1 by Grant Limberg
renamed all instances of MYSQL_ to DRIZZLE_
167
eval load data infile '$DRIZZLE_TEST_DIR/t/loaddata.test' into table t1;
1 by brian
clean slate
168
select * from t1;
169
170
# Test "load_file" returns NULL
319.1.1 by Grant Limberg
renamed all instances of MYSQL_ to DRIZZLE_
171
--replace_result $DRIZZLE_TEST_DIR DRIZZLE_TEST_DIR
172
eval select load_file("$DRIZZLE_TEST_DIR/t/loaddata.test");
1 by brian
clean slate
173
174
# cleanup
175
drop table t1, t2;
176
177
#
178
# Bug#27586: Wrong autoinc value assigned by LOAD DATA in the
179
#            NO_AUTO_VALUE_ON_ZERO mode
180
#
181
create table t1(f1 int);
182
insert into t1 values(1),(null);
183
create table t2(f2 int auto_increment primary key);
184
disable_query_log;
185
eval select * into outfile '$MYSQLTEST_VARDIR/tmp/t1' from t1;
186
SET @OLD_SQL_MODE=@@SQL_MODE, @@SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
187
eval load data infile '$MYSQLTEST_VARDIR/tmp/t1' into table t2;
188
enable_query_log;
189
select * from t2;
190
remove_file $MYSQLTEST_VARDIR/tmp/t1;
191
SET @@SQL_MODE=@OLD_SQL_MODE;
192
drop table t1,t2;
193
194
#
195
# Bug#27670: LOAD DATA does not set CURRENT_TIMESTAMP default value for a
196
#            TIMESTAMP field when no value has been provided.
197
#
198
create table t1(f1 int, f2 timestamp not null default current_timestamp);
199
create table t2(f1 int);
200
insert into t2 values(1),(2);
201
disable_query_log;
202
eval select * into outfile '$MYSQLTEST_VARDIR/tmp/t2' from t2;
203
eval load data infile '$MYSQLTEST_VARDIR/tmp/t2' into table t1;
204
enable_query_log;
205
select f1 from t1 where f2 <> '0000-00-00 00:00:00' order by f1;
206
remove_file $MYSQLTEST_VARDIR/tmp/t2;
207
delete from t1;
208
disable_query_log;
209
eval SELECT * INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/t2'
210
FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\r\n'
211
FROM t2;
212
eval load data infile '$MYSQLTEST_VARDIR/tmp/t2' into table t1
213
FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\r\n';
214
enable_query_log;
215
select f1 from t1 where f2 <> '0000-00-00 00:00:00' order by f1;
216
remove_file $MYSQLTEST_VARDIR/tmp/t2;
217
drop table t1,t2;
218
219
#
220
# Bug#29442: SELECT INTO OUTFILE FIELDS ENCLOSED BY digit, minus sign etc
221
#            corrupts non-string fields containing this character.
222
#
223
224
CREATE TABLE t1 (c1 INT, c2 TIMESTAMP, c3 REAL, c4 DOUBLE);
225
226
INSERT INTO t1 (c1, c2, c3, c4) VALUES (10, '1970-02-01 01:02:03', 1.1E-100, 1.1E+100);
227
SELECT * FROM t1;
228
229
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
230
eval SELECT * INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/t1' FIELDS ENCLOSED BY '-' FROM t1;
231
cat_file $MYSQLTEST_VARDIR/tmp/t1;
232
echo EOF;
233
234
TRUNCATE t1;
235
236
--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
237
eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/t1' INTO TABLE t1 FIELDS ENCLOSED BY '-';
238
SELECT * FROM t1;
239
240
remove_file $MYSQLTEST_VARDIR/tmp/t1;
241
DROP TABLE t1;
242
243
###########################################################################
244
245
--echo
246
--echo # --
247
--echo # -- Bug#35469: server crash with LOAD DATA INFILE to a VIEW.
248
--echo # --
249
250
--echo
251
--disable_warnings
252
DROP TABLE IF EXISTS t1;
253
DROP VIEW IF EXISTS v1;
254
DROP VIEW IF EXISTS v2;
255
DROP VIEW IF EXISTS v3;
256
--enable_warnings
257
258
--echo
259
CREATE TABLE t1(c1 INT, c2 VARCHAR(255));
260
261
--echo
262
CREATE VIEW v1 AS SELECT * FROM t1;
263
CREATE VIEW v2 AS SELECT 1 + 2 AS c0, c1, c2 FROM t1;
264
CREATE VIEW v3 AS SELECT 1 AS d1, 2 AS d2;
265
266
--echo
267
LOAD DATA INFILE '../std_data_ln/bug35469.dat' INTO TABLE v1
268
  FIELDS ESCAPED BY '\\'
269
  TERMINATED BY ','
270
  ENCLOSED BY '"'
271
  LINES TERMINATED BY '\n' (c1, c2);
272
273
--echo
274
SELECT * FROM t1;
275
276
--echo
277
SELECT * FROM v1;
278
279
--echo
280
DELETE FROM t1;
281
282
--echo
283
LOAD DATA INFILE '../std_data_ln/bug35469.dat' INTO TABLE v2
284
  FIELDS ESCAPED BY '\\'
285
  TERMINATED BY ','
286
  ENCLOSED BY '"'
287
  LINES TERMINATED BY '\n' (c1, c2);
288
289
--echo
290
SELECT * FROM t1;
291
292
--echo
293
SELECT * FROM v2;
294
295
--echo
296
DELETE FROM t1;
297
298
--echo
299
--error ER_LOAD_DATA_INVALID_COLUMN
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' (c0, c2);
305
306
--echo
307
--error ER_NON_UPDATABLE_TABLE
308
LOAD DATA INFILE '../std_data_ln/bug35469.dat' INTO TABLE v3
309
  FIELDS ESCAPED BY '\\'
310
  TERMINATED BY ','
311
  ENCLOSED BY '"'
312
  LINES TERMINATED BY '\n' (d1, d2);
313
314
--echo
315
DROP TABLE t1;
316
DROP VIEW v1;
317
DROP VIEW v2;
318
DROP VIEW v3;
319
320
--echo
321
--echo # -- End of Bug#35469.
322
323
###########################################################################
324
325
# End of 5.0 tests
326
327
328
#
329
# Bug#12448 LOAD DATA / SELECT INTO OUTFILE
330
# doesn't work with multibyte path name
331
#
332
CREATE TABLE t1 (a int);
333
INSERT INTO t1 VALUES (1);
334
SET NAMES latin1;
335
SET character_set_filesystem=filename;
336
select @@character_set_filesystem;
337
SELECT * INTO OUTFILE 't-1' FROM t1;
338
DELETE FROM t1;
339
LOAD DATA INFILE 't-1' INTO TABLE t1;
340
SELECT * FROM t1;
341
DELETE FROM t1;
342
SET character_set_filesystem=latin1;
343
select @@character_set_filesystem;
344
LOAD DATA INFILE 't@002d1' INTO TABLE t1;
345
SELECT * FROM t1;
346
DROP TABLE t1;
347
remove_file $MYSQLTEST_VARDIR/master-data/test/t@002d1;
348
SET character_set_filesystem=default;
349
select @@character_set_filesystem;