diff options
Diffstat (limited to 'mysql-test/r/loaddata.result')
| -rw-r--r-- | mysql-test/r/loaddata.result | 121 |
1 files changed, 110 insertions, 11 deletions
diff --git a/mysql-test/r/loaddata.result b/mysql-test/r/loaddata.result index 83a0788b793..0aa95983264 100644 --- a/mysql-test/r/loaddata.result +++ b/mysql-test/r/loaddata.result @@ -1,6 +1,6 @@ -drop table if exists t1,t2; +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 ','; +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 @@ -8,7 +8,7 @@ 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; +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 @@ -16,7 +16,7 @@ a b c d 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); +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 @@ -29,7 +29,7 @@ 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 ''''; +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; @@ -41,11 +41,11 @@ Field 3,'Field 4| |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; +load data infile '../std_data_ln/loaddata3.dat' into table t1 fields terminated by '' enclosed by '' ignore 1 lines; Warnings: -Warning 1265 Data truncated for column 'a' at row 3 +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 1265 Data truncated for column 'a' at row 5 +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 @@ -55,9 +55,10 @@ a b 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; +load data infile '../std_data_ln/loaddata4.dat' into table t1 fields terminated by '' enclosed by '' lines terminated by '' ignore 1 lines; Warnings: -Warning 1265 Data truncated for column 'a' at row 4 +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 @@ -78,7 +79,7 @@ id SET @@SQL_MODE=@OLD_SQL_MODE; drop table t1; create table t1 (a varchar(20), b varchar(20)); -load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by ',' enclosed by '"' escaped by '"' (a,b); +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 @@ -139,3 +140,101 @@ 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/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; |