~drizzle-trunk/drizzle/development

drop table if exists t1, t2;

create table t1 (a date, b date, c date not null, d date);

load data infile '../std_data_ln/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_ln/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_ln/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_ln/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_ln/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_ln/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_ln/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_ln/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_ln/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_ln/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_ln/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_ln/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_ln/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_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));

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_ln/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_ln/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;

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

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_ln/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_ln/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_ln/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_ln/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.

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