path: root/mysql-test/r/loaddata.result

drop table if exists t1, t2;
create table t1 (a date, b date, c date not null, d date);
load data infile '../../std_data/loaddata1.dat' into table t1 fields terminated by ',';
Warnings:
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
load data infile '../../std_data/loaddata1.dat' into table t1 fields terminated by ',' IGNORE 2 LINES;
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
truncate table t1;
load data infile '../../std_data/loaddata1.dat' into table t1 fields terminated by ',' LINES STARTING BY ',' (b,c,d);
Warnings:
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
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
drop table t1;
create table t1 (a text, b text);
load data infile '../../std_data/loaddata2.dat' into table t1 fields terminated by ',' enclosed by '''';
Warnings:
Warning	1261	Row 3 doesn't contain data for all columns
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;
create table t1 (a int, b char(10));
load data infile '../../std_data/loaddata3.dat' into table t1 fields terminated by '' enclosed by '' ignore 1 lines;
Warnings:
Warning	1366	Incorrect integer value: 'error      ' for column 'a' at row 3
Warning	1262	Row 3 was truncated; it contained more data than there were input columns
Warning	1366	Incorrect integer value: 'wrong end  ' for column 'a' at row 5
Warning	1262	Row 5 was truncated; it contained more data than there were input columns
select * from t1;
a	b
1	row 1
2	row 2
0	1234567890
3	row 3
0	1234567890
truncate table t1;
load data infile '../../std_data/loaddata4.dat' into table t1 fields terminated by '' enclosed by '' lines terminated by '' ignore 1 lines;
Warnings:
Warning	1366	Incorrect integer value: '
' for column 'a' at row 4
Warning	1261	Row 4 doesn't contain data for all columns
select * from t1;
a	b
1	row 1
2	row 2
3	row 3
0	
drop table t1;
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;
drop table t1;
create table t1 (a varchar(20), b varchar(20));
load data infile '../../std_data/loaddata_dq.dat' into table t1 fields terminated by ',' enclosed by '"' escaped by '"' (a,b);
select * from t1;
a	b
field1	field2
a"b	cd"ef
a"b	c"d"e
drop table t1;
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.'),
('.r.'), ('.rr.'), ('.rrr.'), ('.rrrr.');
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.
SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1' FIELDS ENCLOSED BY 'r' FROM t1;
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
LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1' INTO TABLE t2 FIELDS ENCLOSED BY 'r';
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;
create table t1 (a int default 100, b int, c varchar(60));
load data infile '../../std_data/rpl_loaddata.dat' into table t1 (a, @b) set b=@b+10, c=concat("b=",@b);
select * from t1;
a	b	c
NULL	20	b=10
NULL	25	b=15
truncate table t1;
load data infile '../../std_data/rpl_loaddata.dat' into table t1 (a, @b) set c= if(a is null,"oops",a);
select * from t1;
a	b	c
NULL	NULL	oops
NULL	NULL	oops
truncate table t1;
set @c:=123;
load data infile '../../std_data/rpl_loaddata.dat' into table t1 (@a, b) set c= if(@a is null,@c,b);
select * from t1;
a	b	c
100	10	123
100	15	123
load data infile '../../std_data/rpl_loaddata.dat' into table t1 (@a, @b);
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;
load data infile '../../std_data/rpl_loaddata.dat' into table t1 set c=b;
Warnings:
Warning	1261	Row 1 doesn't contain data for all columns
Warning	1261	Row 2 doesn't contain data for all columns
select * from t1;
a	b	c
NULL	10	10
NULL	15	15
truncate table t1;
load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (a, b) set c="Wow";
select * from t1;
a	b	c
1	2	Wow
3	4	Wow
5	6	Wow
truncate table t1;
load data infile '../../std_data/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));
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
load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (a, @b);
ERROR HY000: Can't load value from file with fixed size rows to variable
create table t2 (num int primary key, str varchar(10));
insert into t2 values (10,'Ten'), (15,'Fifteen');
truncate table t1;
load data infile '../../std_data/rpl_loaddata.dat' into table t1 (@dummy,@n) set a= @n, c= (select str from t2 where num=@n);
select * from t1;
a	b	c
10	NULL	Ten
15	NULL	Fifteen
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;
load data infile 'MYSQL_TEST_DIR/t/loaddata.test' into table t1;
Got one of the listed errors
select * from t1;
a	b	c
select load_file("MYSQL_TEST_DIR/t/loaddata.test");
load_file("MYSQL_TEST_DIR/t/loaddata.test")
NULL
drop table t1, t2;
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;
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
select f1 from t1 where f2 <> '0000-00-00 00:00:00' order by f1;
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
select f1 from t1 where f2 <> '0000-00-00 00:00:00' order by f1;
f1
1
2
drop table t1,t2;
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.1e100
SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1' FIELDS ENCLOSED BY '-' FROM t1;
-10-	-1970\-02\-01 01:02:03-	-1.1e\-100-	-1.1e100-
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.1e100
DROP TABLE t1;

# --
# -- 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/bug35469.dat' 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/bug35469.dat' 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/bug35469.dat' 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/bug35469.dat' 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.
Bug#37114
SET SESSION character_set_client=latin1;
SET SESSION character_set_server=latin1;
SET SESSION character_set_connection=latin1;
SET @OLD_SQL_MODE=@@SESSION.SQL_MODE;
test LOAD DATA INFILE
SET sql_mode = '';
SELECT '1 \\aa\n' INTO DUMPFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt';
CREATE TABLE t1 (id INT, val1 CHAR(3)) ENGINE=MyISAM;
SET sql_mode = 'NO_BACKSLASH_ESCAPES';
LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' REPLACE INTO TABLE t1 FIELDS TERMINATED BY ' ';
SELECT * FROM t1;
id	val1
1	\aa
SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114_out.txt' FIELDS ESCAPED BY '' TERMINATED BY ' ' FROM t1;
SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114_out.txt' FIELDS               TERMINATED BY ' ' FROM t1;
INSERT INTO t1 (id, val1) VALUES (1, '\aa');
SELECT * FROM t1;
id	val1
1	\aa
1	\aa
SET sql_mode='';
INSERT INTO t1 (id, val1) VALUES (1, '\aa');
SELECT * FROM t1;
id	val1
1	\aa
1	\aa
1	aa
DROP TABLE t1;
test SELECT INTO OUTFILE
CREATE TABLE t1 (id INT PRIMARY KEY, val1 CHAR(4));
CREATE TABLE t2 LIKE t1;
SET sql_mode = '';
INSERT INTO t1 (id, val1) VALUES (5, '\ttab');
INSERT INTO t1 (id, val1) VALUES (4, '\\r');
SET sql_mode = 'NO_BACKSLASH_ESCAPES';
INSERT INTO t1 (id, val1) VALUES (3, '\tx');
1.1 NO_BACKSLASH_ESCAPES, use defaults for ESCAPED BY
SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' FIELDS TERMINATED BY ' ' FROM t1 ORDER BY id;
LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' INTO TABLE t2 FIELDS TERMINATED BY ' ';
SELECT 'before' AS t, id, val1, hex(val1) FROM t1 UNION
SELECT 'after' AS t, id, val1, hex(val1) FROM t2 ORDER BY id,t DESC;
t	id	val1	hex(val1)
before	3	\tx	5C7478
after	3	\tx	5C7478
before	4	\r	5C72
after	4	\r	5C72
before	5		tab	09746162
after	5		tab	09746162
TRUNCATE t2;
SELECT LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt");
LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt")
3 \tx
4 \r
5 	tab

1.2 NO_BACKSLASH_ESCAPES, override defaults for ESCAPED BY
SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' FIELDS ESCAPED BY '\' TERMINATED BY ' ' FROM t1 ORDER BY id;
LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' INTO TABLE t2 FIELDS ESCAPED BY '\' TERMINATED BY ' ';
SELECT 'before' AS t, id, val1, hex(val1) FROM t1 UNION
SELECT 'after' AS t, id, val1, hex(val1) FROM t2 ORDER BY id,t DESC;
t	id	val1	hex(val1)
before	3	\tx	5C7478
after	3	\tx	5C7478
before	4	\r	5C72
after	4	\r	5C72
before	5		tab	09746162
after	5		tab	09746162
TRUNCATE t2;
SELECT LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt");
LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt")
3 \\tx
4 \\r
5 	tab

SET sql_mode = '';
2.1 !NO_BACKSLASH_ESCAPES, use defaults for ESCAPED BY
SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' FIELDS TERMINATED BY ' ' FROM t1 ORDER BY id;
LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' INTO TABLE t2 FIELDS TERMINATED BY ' ';
SELECT 'before' AS t, id, val1, hex(val1) FROM t1 UNION
SELECT 'after' AS t, id, val1, hex(val1) FROM t2 ORDER BY id,t DESC;
t	id	val1	hex(val1)
before	3	\tx	5C7478
after	3	\tx	5C7478
before	4	\r	5C72
after	4	\r	5C72
before	5		tab	09746162
after	5		tab	09746162
TRUNCATE t2;
SET sql_mode = 'NO_BACKSLASH_ESCAPES';
SELECT LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt");
LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt")
3 \\tx
4 \\r
5 	tab

SET sql_mode = '';
2.2 !NO_BACKSLASH_ESCAPES, override defaults for ESCAPED BY
SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' FIELDS ESCAPED BY '' TERMINATED BY ' ' FROM t1 ORDER BY id;
LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' INTO TABLE t2 FIELDS ESCAPED BY '' TERMINATED BY ' ';
SELECT 'before' AS t, id, val1, hex(val1) FROM t1 UNION
SELECT 'after' AS t, id, val1, hex(val1) FROM t2 ORDER BY id,t DESC;
t	id	val1	hex(val1)
before	3	\tx	5C7478
after	3	\tx	5C7478
before	4	\r	5C72
after	4	\r	5C72
before	5		tab	09746162
after	5		tab	09746162
TRUNCATE t2;
SET sql_mode = 'NO_BACKSLASH_ESCAPES';
SELECT LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt");
LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt")
3 \tx
4 \r
5 	tab

set session sql_mode=@OLD_SQL_MODE;
DROP TABLE t1,t2;
End of 5.0 tests
CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES (1);
SET NAMES latin1;
SET character_set_filesystem=filename;
select @@character_set_filesystem;
@@character_set_filesystem
filename
SELECT * INTO OUTFILE 't-1' FROM t1;
DELETE FROM t1;
LOAD DATA INFILE 't-1' INTO TABLE t1;
SELECT * FROM t1;
a
1
DELETE FROM t1;
SET character_set_filesystem=latin1;
select @@character_set_filesystem;
@@character_set_filesystem
latin1
LOAD DATA INFILE 't@002d1' INTO TABLE t1;
SELECT * FROM t1;
a
1
DROP TABLE t1;
SET character_set_filesystem=default;
select @@character_set_filesystem;
@@character_set_filesystem
binary
End of 5.1 tests