path: root/mysql-test/main/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' ignore 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' ignore 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:
Note	1265	Data truncated for column 'a' at row 1
Note	1265	Data truncated for column 'a' at row 2
Warning	1366	Incorrect integer value: 'error      ' for column `test`.`t1`.`a` at row 3
Warning	1262	Row 3 was truncated; it contained more data than there were input columns
Note	1265	Data truncated for column 'a' at row 4
Warning	1366	Incorrect integer value: 'wrong end  ' for column `test`.`t1`.`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:
Note	1265	Data truncated for column 'a' at row 1
Note	1265	Data truncated for column 'a' at row 2
Note	1265	Data truncated for column 'a' at row 3
Warning	1366	Incorrect integer value: '
' for column `test`.`t1`.`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
Warnings:
Note	1265	Data truncated for column 'id' at row 1
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' ignore 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";
Warnings:
Note	1265	Data truncated for column 'a' at row 1
Note	1265	Data truncated for column 'b' at row 1
Note	1265	Data truncated for column 'a' at row 2
Note	1265	Data truncated for column 'b' at row 2
Note	1265	Data truncated for column 'a' at row 3
Note	1265	Data truncated for column 'b' at row 3
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));
Warnings:
Note	1265	Data truncated for column 'a' at row 1
Note	1265	Data truncated for column 'b' at row 1
Note	1265	Data truncated for column 'a' at row 2
Note	1265	Data truncated for column 'b' at row 2
Note	1265	Data truncated for column 'a' at row 3
Note	1265	Data truncated for column 'b' at row 3
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
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:
Note	1265	Data truncated for column 'f1' at row 1
Warning	1261	Row 1 doesn't contain data for all columns
Note	1265	Data truncated for column 'f1' at row 2
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: Column 'c0' is not updatable

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;
#
# Bug #51893: crash with certain characters given to load_file 
#   function on windows
#
select load_file(0x0A9FB76C661B409C4BEC88098C5DD71B1072F9691F2E827D7EC8F092B299868A3CE196C04F0FB18CAB4E1557EB72331D812379DE7A75CA21C32E7C722C59E5CC33EF262EF04187B0F0EE756FA984DF2EAD37B1E4ADB064C3C5038F2E3B2D661B1C1150AAEB5425512E14D7506166D92D4533872E662F4B2D1428AAB5CCA72E75AA2EF325E196A5A02E2E8278873C64375845994B0F39BE2FF7B478332A7B0AA5E48877C47B6F513E997848AF8CCB8A899F3393AB35333CF0871E36698193862D486B4B9078B70C0A0A507B8A250F3F876F5A067632D5E65193E4445A1EC3A2C9B4C6F07AC334F0F62BC33357CB502E9B1C19D2398B6972AEC2EF21630F8C9134C4F7DD662D8AD7BDC9E19C46720F334B66C22D4BF32ED275144E20E7669FFCF6FC143667C9F02A577F32960FA9F2371BE1FA90E49CBC69C01531F140556854D588DD0E55E1307D78CA38E975CD999F9AEA604266329EE62BFB5ADDA67F549E211ECFBA906C60063696352ABB82AA782D25B17E872EA587871F450446DB1BAE0123D20404A8F2D2698B371002E986C8FCB969A99FF0E150A2709E2ED7633D02ADA87D5B3C9487D27B2BD9D21E2EC3215DCC3CDCD884371281B95A2E9987AAF82EB499C058D9C3E7DC1B66635F60DB121C72F929622DD47B6B2E69F59FF2AE6B63CC2EC60FFBA20EA50569DBAB5DAEFAEB4F03966C9637AB55662EDD28439155A82D053A5299448EDB2E7BEB0F62889E2F84E6C7F34B3212C9AAC32D521D5AB8480993F1906D5450FAB342A0FA6ED223E178BAC036B81E15783604C32A961EA1EF20BE2EBB93D34ED37BC03142B7583303E4557E48551E4BD7CBDDEA146D5485A5D212C35189F0BD6497E66912D2780A59A53B532E12C0A5ED1EC0445D96E8F2DD825221CFE4A65A87AA21DC8750481B9849DD81694C3357A0ED9B78D608D8EDDE28FAFBEC17844DE5709F41E121838DB55639D77E32A259A416D7013B2EB1259FDE1B498CBB9CAEE1D601DF3C915EA91C69B44E6B72062F5F4B3C73F06F2D5AD185E1692E2E0A01E7DD5133693681C52EE13B2BE42D03BDCF48E4E133CF06662339B778E1C3034F9939A433E157449172F7969ACCE1F5D2F65A4E09E4A5D5611EBEDDDBDB0C0C0A);
load_file(0x0A9FB76C661B409C4BEC88098C5DD71B1072F9691F2E827D7EC8F092B299868A3CE196C04F0FB18CAB4E1557EB72331D812379DE7A75CA21C32E7C722C59E5CC33EF262EF04187B0F0EE756FA984DF2EAD37B1E4ADB064C3C5038F2E3B2D661B1C1150AAEB5425512E14D7506166D92D4533872E662F4B2D142
NULL
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
#
# Bug #51850: crash/memory overlap when using load data infile and set 
#  col equal to itself!
#
CREATE TABLE t1(col0 LONGBLOB);
SELECT 'test' INTO OUTFILE 't1.txt';
LOAD DATA INFILE 't1.txt' IGNORE INTO TABLE t1 SET col0=col0;
SELECT * FROM t1;
col0
test
DROP TABLE t1;
#
# Bug #52512 : Assertion `! is_set()' in 
#  Diagnostics_area::set_ok_status on LOAD DATA
#
connect  con1,localhost,root,,test;
CREATE TABLE t1 (id INT NOT NULL);
LOAD DATA LOCAL INFILE 'tb.txt' INTO TABLE t1;
connection default;
disconnect con1;
connect  con1,localhost,root,,test;
DROP TABLE t1;
connection default;
disconnect con1;
#
# Bug#11765139  58069: LOAD DATA INFILE: VALGRIND REPORTS INVALID MEMORY READS AND WRITES WITH U
#
CREATE TABLE t1(f1 INT);
SELECT 0xE1BB30 INTO OUTFILE 't1.dat';
LOAD DATA INFILE 't1.dat' IGNORE INTO TABLE t1 CHARACTER SET utf8;
DROP TABLE t1;
#
# Bug#11765141 - 58072: LOAD DATA INFILE: LEAKS IO CACHE MEMORY
# WHEN ERROR OCCURS
#
SELECT '1\n' INTO DUMPFILE 'MYSQLTEST_VARDIR/tmp/bug11735141.txt';
create table t1(a point);
LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug11735141.txt' INTO TABLE t1;
ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field
drop table t1;
End of 5.1 tests
#
# Bug#11759519 INFINITE HANG WITH 100% CPU USAGE WITH LOAD DATA LOCAL AND IMPORT ERRORS
#
SET @old_mode= @@sql_mode;
CREATE TABLE t1 (fld1 INT);
SET sql_mode='strict_all_tables';
# Without fix, load data hangs forever.
LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/mysql' REPLACE INTO TABLE t1
FIELDS TERMINATED BY 't' LINES TERMINATED BY '';
Got one of the listed errors
SET @@sql_mode= @old_mode;
DROP TABLE t1;
#
# MDEV-11079 Regression: LOAD DATA INFILE lost BLOB support using utf8 load files
#
CREATE TABLE t1 (a mediumblob NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
LOAD DATA INFILE '../../std_data/loaddata/mdev-11079.txt' INTO TABLE t1 CHARSET utf8 FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n';
SELECT HEX(a) FROM t1;
HEX(a)
25AAABAC
DROP TABLE t1;
#
# MDEV-11631 LOAD DATA INFILE fails to load data with an escape character followed by a multi-byte character
#
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8);
LOAD DATA INFILE '../../std_data/loaddata/mdev-11631.txt' INTO TABLE t1 CHARACTER SET utf8;
SELECT HEX(a) FROM t1;
HEX(a)
C3A4
DROP TABLE t1;
#
# MDEV-15744: Assertion `derived->table' failed in mysql_derived_merge_for_insert
#
create table t1 (a int, b int);
CREATE OR REPLACE VIEW t2 AS SELECT * FROM t1;
CREATE VIEW v2 AS SELECT * FROM t2;
LOAD DATA INFILE '../../std_data/loaddata7.dat' INTO TABLE v2
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';
select * from v2;
a	b
2	2
3	3
4	4
5	5
6	6
select * from t2;
a	b
2	2
3	3
4	4
5	5
6	6
DROP VIEW IF EXISTS v2,t2;
DROP TABLE IF EXISTS t1;
#
# MDEV-15950: LOAD DATA INTO compex_view crashed
#
create table t1 (a int, b int);
create table t0 (x int, y int);
CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1,t0;
CREATE VIEW v2 AS SELECT * FROM v1;
LOAD DATA INFILE '../../std_data/loaddata7.dat' INTO TABLE v1
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';
ERROR HY000: Incorrect usage of Multi-table VIEW and LOAD
LOAD DATA INFILE '../../std_data/loaddata7.dat' INTO TABLE v2
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';
ERROR HY000: Incorrect usage of Multi-table VIEW and LOAD
DROP VIEW IF EXISTS v2,v1;
DROP TABLE IF EXISTS t1,t0;
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE(b));
INSERT INTO t1 VALUES (1,1);
CREATE TABLE t2 (c INT);
CREATE VIEW v AS SELECT t1.* FROM t1 JOIN t2;
SELECT a, b FROM t1 INTO OUTFILE '15645.data';
LOAD DATA INFILE '15645.data' IGNORE INTO TABLE v (a,b);
ERROR HY000: Incorrect usage of Multi-table VIEW and LOAD
LOAD DATA INFILE '15645.data' REPLACE INTO TABLE v (a,b);
ERROR HY000: Incorrect usage of Multi-table VIEW and LOAD
drop table t1,t2;
drop view v;
#
# MDEV-15497 Wrong empty value in a GEOMETRY column on LOAD DATA
#
SET sql_mode='';
CREATE TABLE t1 (a CHAR(1), b CHAR(1) NOT NULL);
LOAD DATA INFILE '../../std_data/loaddata/mdev-15497.txt' INTO TABLE t1;
Warnings:
Warning	1261	Row 1 doesn't contain data for all columns
SELECT * FROM t1;
a	b
1	
TRUNCATE TABLE t1;
LOAD DATA INFILE '../../std_data/loaddata/mdev-15497.txt' INTO TABLE t1 FIELDS TERMINATED BY '';
Warnings:
Warning	1261	Row 1 doesn't contain data for all columns
SELECT * FROM t1;
a	b
1	
DROP TABLE t1;
CREATE TABLE t1 (a CHAR(1), b CHAR(1));
LOAD DATA INFILE '../../std_data/loaddata/mdev-15497.txt' INTO TABLE t1;
Warnings:
Warning	1261	Row 1 doesn't contain data for all columns
SELECT * FROM t1;
a	b
1	NULL
TRUNCATE TABLE t1;
LOAD DATA INFILE '../../std_data/loaddata/mdev-15497.txt' INTO TABLE t1 FIELDS TERMINATED BY '';
Warnings:
Warning	1261	Row 1 doesn't contain data for all columns
SELECT * FROM t1;
a	b
1	
DROP TABLE t1;
#
# MDEV-15597 Add class Load_data_outvar and avoid using Item::STRING_ITEM for Item_user_var_as_out_param detection
#
SET sql_mode=NO_AUTO_VALUE_ON_ZERO;
CREATE TABLE t1 (id integer not null auto_increment primary key);
LOAD DATA INFILE '../../std_data/loaddata/nl.txt' INTO TABLE t1 FIELDS TERMINATED BY '';
Warnings:
Warning	1261	Row 1 doesn't contain data for all columns
SELECT * FROM t1;
id
0
DROP TABLE t1;
SET sql_mode='';
CREATE TABLE t1 (id integer not null auto_increment primary key);
LOAD DATA INFILE '../../std_data/loaddata/nl.txt' INTO TABLE t1 FIELDS TERMINATED BY '';
Warnings:
Warning	1261	Row 1 doesn't contain data for all columns
SELECT * FROM t1;
id
1
DROP TABLE t1;