loaddata.result 8.94 KB
Newer Older
1
drop table if exists t1, t2;
2
create table t1 (a date, b date, c date not null, d date);
3
load data infile '../std_data_ln/loaddata1.dat' into table t1 fields terminated by ',';
unknown's avatar
unknown committed
4
Warnings:
unknown's avatar
unknown committed
5 6 7 8 9 10
Warning	1265	Data truncated for column 'a' at row 1
Warning	1265	Data truncated for column 'c' at row 1
Warning	1265	Data truncated for column 'd' at row 1
Warning	1265	Data truncated for column 'a' at row 2
Warning	1265	Data truncated for column 'b' at row 2
Warning	1265	Data truncated for column 'd' at row 2
11
load data infile '../std_data_ln/loaddata1.dat' into table t1 fields terminated by ',' IGNORE 2 LINES;
12 13 14 15 16 17
SELECT * from t1;
a	b	c	d
0000-00-00	NULL	0000-00-00	0000-00-00
0000-00-00	0000-00-00	0000-00-00	0000-00-00
2003-03-03	2003-03-03	2003-03-03	NULL
2003-03-03	2003-03-03	2003-03-03	NULL
18
truncate table t1;
19
load data infile '../std_data_ln/loaddata1.dat' into table t1 fields terminated by ',' LINES STARTING BY ',' (b,c,d);
unknown's avatar
unknown committed
20
Warnings:
unknown's avatar
unknown committed
21 22 23 24
Warning	1265	Data truncated for column 'c' at row 1
Warning	1265	Data truncated for column 'd' at row 1
Warning	1265	Data truncated for column 'b' at row 2
Warning	1265	Data truncated for column 'd' at row 2
25 26 27 28 29
SELECT * from t1;
a	b	c	d
NULL	NULL	0000-00-00	0000-00-00
NULL	0000-00-00	0000-00-00	0000-00-00
NULL	2003-03-03	2003-03-03	NULL
30
drop table t1;
31
create table t1 (a text, b text);
32
load data infile '../std_data_ln/loaddata2.dat' into table t1 fields terminated by ',' enclosed by '''';
unknown's avatar
unknown committed
33
Warnings:
unknown's avatar
unknown committed
34
Warning	1261	Row 3 doesn't contain data for all columns
35 36 37 38 39 40 41 42
select concat('|',a,'|'), concat('|',b,'|') from t1;
concat('|',a,'|')	concat('|',b,'|')
|Field A|	|Field B|
|Field 1|	|Field 2' 
Field 3,'Field 4|
|Field 5' ,'Field 6|	NULL
|Field 6|	| 'Field 7'|
drop table t1;
43
create table t1 (a int, b char(10));
44
load data infile '../std_data_ln/loaddata3.dat' into table t1 fields terminated by '' enclosed by '' ignore 1 lines;
unknown's avatar
unknown committed
45
Warnings:
46
Warning	1366	Incorrect integer value: 'error      ' for column 'a' at row 3
47
Warning	1262	Row 3 was truncated; it contained more data than there were input columns
48
Warning	1366	Incorrect integer value: 'wrong end  ' for column 'a' at row 5
49
Warning	1262	Row 5 was truncated; it contained more data than there were input columns
50 51 52 53 54 55 56 57
select * from t1;
a	b
1	row 1
2	row 2
0	1234567890
3	row 3
0	1234567890
truncate table t1;
58
load data infile '../std_data_ln/loaddata4.dat' into table t1 fields terminated by '' enclosed by '' lines terminated by '' ignore 1 lines;
unknown's avatar
unknown committed
59
Warnings:
60 61
Warning	1366	Incorrect integer value: '
' for column 'a' at row 4
unknown's avatar
unknown committed
62
Warning	1261	Row 4 doesn't contain data for all columns
63 64 65 66 67 68 69
select * from t1;
a	b
1	row 1
2	row 2
3	row 3
0	
drop table t1;
70 71 72 73 74 75 76 77 78 79
SET @OLD_SQL_MODE=@@SQL_MODE, @@SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
create table t1(id integer not null auto_increment primary key);
insert into t1 values(0);
select * from t1;
id
0
select * from t1;
id
0
SET @@SQL_MODE=@OLD_SQL_MODE;
unknown's avatar
unknown committed
80
drop table t1;
81
create table t1 (a varchar(20), b varchar(20));
82
load data infile '../std_data_ln/loaddata_dq.dat' into table t1 fields terminated by ',' enclosed by '"' escaped by '"' (a,b);
83 84 85 86 87 88
select * from t1;
a	b
field1	field2
a"b	cd"ef
a"b	c"d"e
drop table t1;
unknown's avatar
unknown committed
89 90 91 92 93 94 95 96 97 98 99 100
CREATE TABLE t1 (
id INT AUTO_INCREMENT PRIMARY KEY,
c1 VARCHAR(255)
);
CREATE TABLE t2 (
id INT,
c2 VARCHAR(255)
);
INSERT INTO t1 (c1) VALUES
('r'),   ('rr'),   ('rrr'),   ('rrrr'),
('.r'),  ('.rr'),  ('.rrr'),  ('.rrrr'),
('r.'),  ('rr.'),  ('rrr.'),  ('rrrr.'),
unknown's avatar
unknown committed
101
('.r.'), ('.rr.'), ('.rrr.'), ('.rrrr.');
unknown's avatar
unknown committed
102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119
SELECT * FROM t1;
id	c1
1	r
2	rr
3	rrr
4	rrrr
5	.r
6	.rr
7	.rrr
8	.rrrr
9	r.
10	rr.
11	rrr.
12	rrrr.
13	.r.
14	.rr.
15	.rrr.
16	.rrrr.
unknown's avatar
unknown committed
120
SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1' FIELDS ENCLOSED BY 'r' FROM t1;
unknown's avatar
unknown committed
121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136
r1r	rrrr
r2r	rrrrrr
r3r	rrrrrrrr
r4r	rrrrrrrrrr
r5r	r.rrr
r6r	r.rrrrr
r7r	r.rrrrrrr
r8r	r.rrrrrrrrr
r9r	rrr.r
r10r	rrrrr.r
r11r	rrrrrrr.r
r12r	rrrrrrrrr.r
r13r	r.rr.r
r14r	r.rrrr.r
r15r	r.rrrrrr.r
r16r	r.rrrrrrrr.r
unknown's avatar
unknown committed
137
LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1' INTO TABLE t2 FIELDS ENCLOSED BY 'r';
unknown's avatar
unknown committed
138 139 140 141 142
SELECT t1.id, c1, c2 FROM t1 LEFT  JOIN t2 ON t1.id=t2.id WHERE c1 != c2;
id	c1	c2
SELECT t1.id, c1, c2 FROM t1 RIGHT JOIN t2 ON t1.id=t2.id WHERE c1 != c2;
id	c1	c2
DROP TABLE t1,t2;
unknown's avatar
unknown committed
143
create table t1 (a int default 100, b int, c varchar(60));
144
load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 (a, @b) set b=@b+10, c=concat("b=",@b);
unknown's avatar
unknown committed
145 146 147 148 149
select * from t1;
a	b	c
NULL	20	b=10
NULL	25	b=15
truncate table t1;
150
load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 (a, @b) set c= if(a is null,"oops",a);
unknown's avatar
unknown committed
151 152 153 154 155 156
select * from t1;
a	b	c
NULL	NULL	oops
NULL	NULL	oops
truncate table t1;
set @c:=123;
157
load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 (@a, b) set c= if(@a is null,@c,b);
unknown's avatar
unknown committed
158 159 160 161
select * from t1;
a	b	c
100	10	123
100	15	123
162
load data infile '../std_data_ln/rpl_loaddata.dat' into table t1 (@a, @b);
unknown's avatar
unknown committed
163 164 165 166 167 168 169 170 171 172
select * from t1;
a	b	c
100	10	123
100	15	123
100	NULL	NULL
100	NULL	NULL
select @a, @b;
@a	@b
NULL	15
truncate table t1;
173
load data infile '../std_data_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (a, b) set c="Wow";
unknown's avatar
unknown committed
174 175 176 177 178 179
select * from t1;
a	b	c
1	2	Wow
3	4	Wow
5	6	Wow
truncate table t1;
180
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));
unknown's avatar
unknown committed
181 182 183 184 185
select * from t1;
a	b	c
1	2	1+2+123+2+NIL
3	4	3+4+123+4+NIL
5	6	5+6+123+6+NIL
186
load data infile '../std_data_ln/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (a, @b);
unknown's avatar
unknown committed
187
ERROR HY000: Can't load value from file with fixed size rows to variable
188 189 190
create table t2 (num int primary key, str varchar(10));
insert into t2 values (10,'Ten'), (15,'Fifteen');
truncate table t1;
191
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);
192 193 194 195
select * from t1;
a	b	c
10	NULL	Ten
15	NULL	Fifteen
196 197 198 199 200 201 202 203 204
show variables like "secure_file_pri%";
Variable_name	Value
secure_file_priv	MYSQLTEST_VARDIR/
select @@secure_file_priv;
@@secure_file_priv
MYSQLTEST_VARDIR/
set @@secure_file_priv= 0;
ERROR HY000: Variable 'secure_file_priv' is a read only variable
truncate table t1;
205
load data infile 'MYSQL_TEST_DIR/t/loaddata.test' into table t1;
206 207 208
ERROR HY000: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
select * from t1;
a	b	c
209 210
select load_file("MYSQL_TEST_DIR/t/loaddata.test");
load_file("MYSQL_TEST_DIR/t/loaddata.test")
211
NULL
212
drop table t1, t2;
213 214 215 216 217 218 219 220 221
create table t1(f1 int);
insert into t1 values(1),(null);
create table t2(f2 int auto_increment primary key);
select * from t2;
f2
1
2
SET @@SQL_MODE=@OLD_SQL_MODE;
drop table t1,t2;
222 223 224 225 226 227
create table t1(f1 int, f2 timestamp not null default current_timestamp);
create table t2(f1 int);
insert into t2 values(1),(2);
Warnings:
Warning	1261	Row 1 doesn't contain data for all columns
Warning	1261	Row 2 doesn't contain data for all columns
unknown's avatar
unknown committed
228
select f1 from t1 where f2 <> '0000-00-00 00:00:00' order by f1;
229 230 231 232 233 234 235
f1
1
2
delete from t1;
Warnings:
Warning	1261	Row 1 doesn't contain data for all columns
Warning	1261	Row 2 doesn't contain data for all columns
unknown's avatar
unknown committed
236
select f1 from t1 where f2 <> '0000-00-00 00:00:00' order by f1;
237 238 239 240
f1
1
2
drop table t1,t2;
unknown's avatar
unknown committed
241 242 243 244 245 246 247 248 249 250 251 252 253 254
CREATE TABLE t1 (c1 INT, c2 TIMESTAMP, c3 REAL, c4 DOUBLE);
INSERT INTO t1 (c1, c2, c3, c4) VALUES (10, '1970-02-01 01:02:03', 1.1E-100, 1.1E+100);
SELECT * FROM t1;
c1	c2	c3	c4
10	1970-02-01 01:02:03	1.1e-100	1.1e+100
SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1' FIELDS ENCLOSED BY '-' FROM t1;
-10-	-1970\-02\-01 01:02:03-	-1.1e\-100-	-1.1e+100-
EOF
TRUNCATE t1;
LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1' INTO TABLE t1 FIELDS ENCLOSED BY '-';
SELECT * FROM t1;
c1	c2	c3	c4
10	1970-02-01 01:02:03	1.1e-100	1.1e+100
DROP TABLE t1;
255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330

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

DROP TABLE IF EXISTS t1;
DROP VIEW IF EXISTS v1;
DROP VIEW IF EXISTS v2;
DROP VIEW IF EXISTS v3;

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

CREATE VIEW v1 AS SELECT * FROM t1;
CREATE VIEW v2 AS SELECT 1 + 2 AS c0, c1, c2 FROM t1;
CREATE VIEW v3 AS SELECT 1 AS d1, 2 AS d2;

LOAD DATA INFILE '../std_data_ln/bug35649.data' INTO TABLE v1
FIELDS ESCAPED BY '\\'
  TERMINATED BY ','
  ENCLOSED BY '"'
  LINES TERMINATED BY '\n' (c1, c2);

SELECT * FROM t1;
c1	c2
1	 "string1"
2	 "string2"
3	 "string3"

SELECT * FROM v1;
c1	c2
1	 "string1"
2	 "string2"
3	 "string3"

DELETE FROM t1;

LOAD DATA INFILE '../std_data_ln/bug35649.data' INTO TABLE v2
FIELDS ESCAPED BY '\\'
  TERMINATED BY ','
  ENCLOSED BY '"'
  LINES TERMINATED BY '\n' (c1, c2);

SELECT * FROM t1;
c1	c2
1	 "string1"
2	 "string2"
3	 "string3"

SELECT * FROM v2;
c0	c1	c2
3	1	 "string1"
3	2	 "string2"
3	3	 "string3"

DELETE FROM t1;

LOAD DATA INFILE '../std_data_ln/bug35649.data' INTO TABLE v2
FIELDS ESCAPED BY '\\'
  TERMINATED BY ','
  ENCLOSED BY '"'
  LINES TERMINATED BY '\n' (c0, c2);
ERROR HY000: Invalid column reference (v2.c0) in LOAD DATA

LOAD DATA INFILE '../std_data_ln/bug35649.data' INTO TABLE v3
FIELDS ESCAPED BY '\\'
  TERMINATED BY ','
  ENCLOSED BY '"'
  LINES TERMINATED BY '\n' (d1, d2);
ERROR HY000: The target table v3 of the LOAD is not updatable

DROP TABLE t1;
DROP VIEW v1;
DROP VIEW v2;
DROP VIEW v3;

# -- End of Bug#35469.