1drop table if exists t1, t2; 2create table t1 (a date, b date, c date not null, d date); 3load data infile '../../std_data/loaddata1.dat' ignore into table t1 fields terminated by ','; 4Warnings: 5Warning 1265 Data truncated for column 'a' at row 1 6Warning 1265 Data truncated for column 'c' at row 1 7Warning 1265 Data truncated for column 'd' at row 1 8Warning 1265 Data truncated for column 'a' at row 2 9Warning 1265 Data truncated for column 'b' at row 2 10Warning 1265 Data truncated for column 'd' at row 2 11load data infile '../../std_data/loaddata1.dat' into table t1 fields terminated by ',' IGNORE 2 LINES; 12SELECT * from t1; 13a b c d 140000-00-00 NULL 0000-00-00 0000-00-00 150000-00-00 0000-00-00 0000-00-00 0000-00-00 162003-03-03 2003-03-03 2003-03-03 NULL 172003-03-03 2003-03-03 2003-03-03 NULL 18truncate table t1; 19load data infile '../../std_data/loaddata1.dat' ignore into table t1 fields terminated by ',' LINES STARTING BY ',' (b,c,d); 20Warnings: 21Warning 1265 Data truncated for column 'c' at row 1 22Warning 1265 Data truncated for column 'd' at row 1 23Warning 1265 Data truncated for column 'b' at row 2 24Warning 1265 Data truncated for column 'd' at row 2 25SELECT * from t1; 26a b c d 27NULL NULL 0000-00-00 0000-00-00 28NULL 0000-00-00 0000-00-00 0000-00-00 29NULL 2003-03-03 2003-03-03 NULL 30drop table t1; 31create table t1 (a text, b text); 32load data infile '../../std_data/loaddata2.dat' into table t1 fields terminated by ',' enclosed by ''''; 33Warnings: 34Warning 1261 Row 3 doesn't contain data for all columns 35select concat('|',a,'|'), concat('|',b,'|') from t1; 36concat('|',a,'|') concat('|',b,'|') 37|Field A| |Field B| 38|Field 1| |Field 2' 39Field 3,'Field 4| 40|Field 5' ,'Field 6| NULL 41|Field 6| | 'Field 7'| 42drop table t1; 43create table t1 (a int, b char(10)); 44load data infile '../../std_data/loaddata3.dat' into table t1 fields terminated by '' enclosed by '' ignore 1 lines; 45Warnings: 46Note 1265 Data truncated for column 'a' at row 1 47Note 1265 Data truncated for column 'a' at row 2 48Warning 1366 Incorrect integer value: 'error ' for column `test`.`t1`.`a` at row 3 49Warning 1262 Row 3 was truncated; it contained more data than there were input columns 50Note 1265 Data truncated for column 'a' at row 4 51Warning 1366 Incorrect integer value: 'wrong end ' for column `test`.`t1`.`a` at row 5 52Warning 1262 Row 5 was truncated; it contained more data than there were input columns 53select * from t1; 54a b 551 row 1 562 row 2 570 1234567890 583 row 3 590 1234567890 60truncate table t1; 61load data infile '../../std_data/loaddata4.dat' into table t1 fields terminated by '' enclosed by '' lines terminated by '' ignore 1 lines; 62Warnings: 63Note 1265 Data truncated for column 'a' at row 1 64Note 1265 Data truncated for column 'a' at row 2 65Note 1265 Data truncated for column 'a' at row 3 66Warning 1366 Incorrect integer value: ' 67' for column `test`.`t1`.`a` at row 4 68Warning 1261 Row 4 doesn't contain data for all columns 69select * from t1; 70a b 711 row 1 722 row 2 733 row 3 740 75drop table t1; 76SET @OLD_SQL_MODE=@@SQL_MODE, @@SQL_MODE=NO_AUTO_VALUE_ON_ZERO; 77create table t1(id integer not null auto_increment primary key); 78insert into t1 values(0); 79select * from t1; 80id 810 82Warnings: 83Note 1265 Data truncated for column 'id' at row 1 84select * from t1; 85id 860 87SET @@SQL_MODE=@OLD_SQL_MODE; 88drop table t1; 89create table t1 (a varchar(20), b varchar(20)); 90load data infile '../../std_data/loaddata_dq.dat' into table t1 fields terminated by ',' enclosed by '"' escaped by '"' (a,b); 91select * from t1; 92a b 93field1 field2 94a"b cd"ef 95a"b c"d"e 96drop table t1; 97CREATE TABLE t1 ( 98id INT AUTO_INCREMENT PRIMARY KEY, 99c1 VARCHAR(255) 100); 101CREATE TABLE t2 ( 102id INT, 103c2 VARCHAR(255) 104); 105INSERT INTO t1 (c1) VALUES 106('r'), ('rr'), ('rrr'), ('rrrr'), 107('.r'), ('.rr'), ('.rrr'), ('.rrrr'), 108('r.'), ('rr.'), ('rrr.'), ('rrrr.'), 109('.r.'), ('.rr.'), ('.rrr.'), ('.rrrr.'); 110SELECT * FROM t1; 111id c1 1121 r 1132 rr 1143 rrr 1154 rrrr 1165 .r 1176 .rr 1187 .rrr 1198 .rrrr 1209 r. 12110 rr. 12211 rrr. 12312 rrrr. 12413 .r. 12514 .rr. 12615 .rrr. 12716 .rrrr. 128SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1' FIELDS ENCLOSED BY 'r' FROM t1; 129r1r rrrr 130r2r rrrrrr 131r3r rrrrrrrr 132r4r rrrrrrrrrr 133r5r r.rrr 134r6r r.rrrrr 135r7r r.rrrrrrr 136r8r r.rrrrrrrrr 137r9r rrr.r 138r10r rrrrr.r 139r11r rrrrrrr.r 140r12r rrrrrrrrr.r 141r13r r.rr.r 142r14r r.rrrr.r 143r15r r.rrrrrr.r 144r16r r.rrrrrrrr.r 145LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1' INTO TABLE t2 FIELDS ENCLOSED BY 'r'; 146SELECT t1.id, c1, c2 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE c1 != c2; 147id c1 c2 148SELECT t1.id, c1, c2 FROM t1 RIGHT JOIN t2 ON t1.id=t2.id WHERE c1 != c2; 149id c1 c2 150DROP TABLE t1,t2; 151create table t1 (a int default 100, b int, c varchar(60)); 152load data infile '../../std_data/rpl_loaddata.dat' into table t1 (a, @b) set b=@b+10, c=concat("b=",@b); 153select * from t1; 154a b c 155NULL 20 b=10 156NULL 25 b=15 157truncate table t1; 158load data infile '../../std_data/rpl_loaddata.dat' into table t1 (a, @b) set c= if(a is null,"oops",a); 159select * from t1; 160a b c 161NULL NULL oops 162NULL NULL oops 163truncate table t1; 164set @c:=123; 165load data infile '../../std_data/rpl_loaddata.dat' into table t1 (@a, b) set c= if(@a is null,@c,b); 166select * from t1; 167a b c 168100 10 123 169100 15 123 170load data infile '../../std_data/rpl_loaddata.dat' into table t1 (@a, @b); 171select * from t1; 172a b c 173100 10 123 174100 15 123 175100 NULL NULL 176100 NULL NULL 177select @a, @b; 178@a @b 179NULL 15 180truncate table t1; 181load data infile '../../std_data/rpl_loaddata.dat' ignore into table t1 set c=b; 182Warnings: 183Warning 1261 Row 1 doesn't contain data for all columns 184Warning 1261 Row 2 doesn't contain data for all columns 185select * from t1; 186a b c 187NULL 10 10 188NULL 15 15 189truncate table t1; 190load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (a, b) set c="Wow"; 191Warnings: 192Note 1265 Data truncated for column 'a' at row 1 193Note 1265 Data truncated for column 'b' at row 1 194Note 1265 Data truncated for column 'a' at row 2 195Note 1265 Data truncated for column 'b' at row 2 196Note 1265 Data truncated for column 'a' at row 3 197Note 1265 Data truncated for column 'b' at row 3 198select * from t1; 199a b c 2001 2 Wow 2013 4 Wow 2025 6 Wow 203truncate table t1; 204load 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)); 205Warnings: 206Note 1265 Data truncated for column 'a' at row 1 207Note 1265 Data truncated for column 'b' at row 1 208Note 1265 Data truncated for column 'a' at row 2 209Note 1265 Data truncated for column 'b' at row 2 210Note 1265 Data truncated for column 'a' at row 3 211Note 1265 Data truncated for column 'b' at row 3 212select * from t1; 213a b c 2141 2 1+2+123+2+NIL 2153 4 3+4+123+4+NIL 2165 6 5+6+123+6+NIL 217load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (a, @b); 218ERROR HY000: Can't load value from file with fixed size rows to variable 219create table t2 (num int primary key, str varchar(10)); 220insert into t2 values (10,'Ten'), (15,'Fifteen'); 221truncate table t1; 222load data infile '../../std_data/rpl_loaddata.dat' into table t1 (@dummy,@n) set a= @n, c= (select str from t2 where num=@n); 223select * from t1; 224a b c 22510 NULL Ten 22615 NULL Fifteen 227set @@secure_file_priv= 0; 228ERROR HY000: Variable 'secure_file_priv' is a read only variable 229truncate table t1; 230load data infile 'MYSQL_TEST_DIR/t/loaddata.test' into table t1; 231Got one of the listed errors 232select * from t1; 233a b c 234select load_file("MYSQL_TEST_DIR/t/loaddata.test"); 235load_file("MYSQL_TEST_DIR/t/loaddata.test") 236NULL 237drop table t1, t2; 238create table t1(f1 int); 239insert into t1 values(1),(null); 240create table t2(f2 int auto_increment primary key); 241select * from t2; 242f2 2431 2442 245SET @@SQL_MODE=@OLD_SQL_MODE; 246drop table t1,t2; 247create table t1(f1 int, f2 timestamp not null default current_timestamp); 248create table t2(f1 int); 249insert into t2 values(1),(2); 250Warnings: 251Warning 1261 Row 1 doesn't contain data for all columns 252Warning 1261 Row 2 doesn't contain data for all columns 253select f1 from t1 where f2 <> '0000-00-00 00:00:00' order by f1; 254f1 2551 2562 257delete from t1; 258Warnings: 259Note 1265 Data truncated for column 'f1' at row 1 260Warning 1261 Row 1 doesn't contain data for all columns 261Note 1265 Data truncated for column 'f1' at row 2 262Warning 1261 Row 2 doesn't contain data for all columns 263select f1 from t1 where f2 <> '0000-00-00 00:00:00' order by f1; 264f1 2651 2662 267drop table t1,t2; 268CREATE TABLE t1 (c1 INT, c2 TIMESTAMP, c3 REAL, c4 DOUBLE); 269INSERT INTO t1 (c1, c2, c3, c4) VALUES (10, '1970-02-01 01:02:03', 1.1E-100, 1.1E+100); 270SELECT * FROM t1; 271c1 c2 c3 c4 27210 1970-02-01 01:02:03 1.1e-100 1.1e100 273SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1' FIELDS ENCLOSED BY '-' FROM t1; 274-10- -1970\-02\-01 01:02:03- -1.1e\-100- -1.1e100- 275EOF 276TRUNCATE t1; 277LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1' INTO TABLE t1 FIELDS ENCLOSED BY '-'; 278SELECT * FROM t1; 279c1 c2 c3 c4 28010 1970-02-01 01:02:03 1.1e-100 1.1e100 281DROP TABLE t1; 282 283# -- 284# -- Bug#35469: server crash with LOAD DATA INFILE to a VIEW. 285# -- 286 287DROP TABLE IF EXISTS t1; 288DROP VIEW IF EXISTS v1; 289DROP VIEW IF EXISTS v2; 290DROP VIEW IF EXISTS v3; 291 292CREATE TABLE t1(c1 INT, c2 VARCHAR(255)); 293 294CREATE VIEW v1 AS SELECT * FROM t1; 295CREATE VIEW v2 AS SELECT 1 + 2 AS c0, c1, c2 FROM t1; 296CREATE VIEW v3 AS SELECT 1 AS d1, 2 AS d2; 297 298LOAD DATA INFILE '../../std_data/bug35469.dat' INTO TABLE v1 299FIELDS ESCAPED BY '\\' 300 TERMINATED BY ',' 301 ENCLOSED BY '"' 302 LINES TERMINATED BY '\n' (c1, c2); 303 304SELECT * FROM t1; 305c1 c2 3061 "string1" 3072 "string2" 3083 "string3" 309 310SELECT * FROM v1; 311c1 c2 3121 "string1" 3132 "string2" 3143 "string3" 315 316DELETE FROM t1; 317 318LOAD DATA INFILE '../../std_data/bug35469.dat' INTO TABLE v2 319FIELDS ESCAPED BY '\\' 320 TERMINATED BY ',' 321 ENCLOSED BY '"' 322 LINES TERMINATED BY '\n' (c1, c2); 323 324SELECT * FROM t1; 325c1 c2 3261 "string1" 3272 "string2" 3283 "string3" 329 330SELECT * FROM v2; 331c0 c1 c2 3323 1 "string1" 3333 2 "string2" 3343 3 "string3" 335 336DELETE FROM t1; 337 338LOAD DATA INFILE '../../std_data/bug35469.dat' INTO TABLE v2 339FIELDS ESCAPED BY '\\' 340 TERMINATED BY ',' 341 ENCLOSED BY '"' 342 LINES TERMINATED BY '\n' (c0, c2); 343ERROR HY000: Column 'c0' is not updatable 344 345LOAD DATA INFILE '../../std_data/bug35469.dat' INTO TABLE v3 346FIELDS ESCAPED BY '\\' 347 TERMINATED BY ',' 348 ENCLOSED BY '"' 349 LINES TERMINATED BY '\n' (d1, d2); 350ERROR HY000: The target table v3 of the LOAD is not updatable 351 352DROP TABLE t1; 353DROP VIEW v1; 354DROP VIEW v2; 355DROP VIEW v3; 356 357# -- End of Bug#35469. 358Bug#37114 359SET SESSION character_set_client=latin1; 360SET SESSION character_set_server=latin1; 361SET SESSION character_set_connection=latin1; 362SET @OLD_SQL_MODE=@@SESSION.SQL_MODE; 363test LOAD DATA INFILE 364SET sql_mode = ''; 365SELECT '1 \\aa\n' INTO DUMPFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt'; 366CREATE TABLE t1 (id INT, val1 CHAR(3)) ENGINE=MyISAM; 367SET sql_mode = 'NO_BACKSLASH_ESCAPES'; 368LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' REPLACE INTO TABLE t1 FIELDS TERMINATED BY ' '; 369SELECT * FROM t1; 370id val1 3711 \aa 372SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114_out.txt' FIELDS ESCAPED BY '' TERMINATED BY ' ' FROM t1; 373SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114_out.txt' FIELDS TERMINATED BY ' ' FROM t1; 374INSERT INTO t1 (id, val1) VALUES (1, '\aa'); 375SELECT * FROM t1; 376id val1 3771 \aa 3781 \aa 379SET sql_mode=''; 380INSERT INTO t1 (id, val1) VALUES (1, '\aa'); 381SELECT * FROM t1; 382id val1 3831 \aa 3841 \aa 3851 aa 386DROP TABLE t1; 387test SELECT INTO OUTFILE 388CREATE TABLE t1 (id INT PRIMARY KEY, val1 CHAR(4)); 389CREATE TABLE t2 LIKE t1; 390SET sql_mode = ''; 391INSERT INTO t1 (id, val1) VALUES (5, '\ttab'); 392INSERT INTO t1 (id, val1) VALUES (4, '\\r'); 393SET sql_mode = 'NO_BACKSLASH_ESCAPES'; 394INSERT INTO t1 (id, val1) VALUES (3, '\tx'); 3951.1 NO_BACKSLASH_ESCAPES, use defaults for ESCAPED BY 396SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' FIELDS TERMINATED BY ' ' FROM t1 ORDER BY id; 397LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' INTO TABLE t2 FIELDS TERMINATED BY ' '; 398SELECT 'before' AS t, id, val1, hex(val1) FROM t1 UNION 399SELECT 'after' AS t, id, val1, hex(val1) FROM t2 ORDER BY id,t DESC; 400t id val1 hex(val1) 401before 3 \tx 5C7478 402after 3 \tx 5C7478 403before 4 \r 5C72 404after 4 \r 5C72 405before 5 tab 09746162 406after 5 tab 09746162 407TRUNCATE t2; 408SELECT LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt"); 409LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt") 4103 \tx 4114 \r 4125 tab 413 4141.2 NO_BACKSLASH_ESCAPES, override defaults for ESCAPED BY 415SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' FIELDS ESCAPED BY '\' TERMINATED BY ' ' FROM t1 ORDER BY id; 416LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' INTO TABLE t2 FIELDS ESCAPED BY '\' TERMINATED BY ' '; 417SELECT 'before' AS t, id, val1, hex(val1) FROM t1 UNION 418SELECT 'after' AS t, id, val1, hex(val1) FROM t2 ORDER BY id,t DESC; 419t id val1 hex(val1) 420before 3 \tx 5C7478 421after 3 \tx 5C7478 422before 4 \r 5C72 423after 4 \r 5C72 424before 5 tab 09746162 425after 5 tab 09746162 426TRUNCATE t2; 427SELECT LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt"); 428LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt") 4293 \\tx 4304 \\r 4315 tab 432 433SET sql_mode = ''; 4342.1 !NO_BACKSLASH_ESCAPES, use defaults for ESCAPED BY 435SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' FIELDS TERMINATED BY ' ' FROM t1 ORDER BY id; 436LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' INTO TABLE t2 FIELDS TERMINATED BY ' '; 437SELECT 'before' AS t, id, val1, hex(val1) FROM t1 UNION 438SELECT 'after' AS t, id, val1, hex(val1) FROM t2 ORDER BY id,t DESC; 439t id val1 hex(val1) 440before 3 \tx 5C7478 441after 3 \tx 5C7478 442before 4 \r 5C72 443after 4 \r 5C72 444before 5 tab 09746162 445after 5 tab 09746162 446TRUNCATE t2; 447SET sql_mode = 'NO_BACKSLASH_ESCAPES'; 448SELECT LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt"); 449LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt") 4503 \\tx 4514 \\r 4525 tab 453 454SET sql_mode = ''; 4552.2 !NO_BACKSLASH_ESCAPES, override defaults for ESCAPED BY 456SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' FIELDS ESCAPED BY '' TERMINATED BY ' ' FROM t1 ORDER BY id; 457LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug37114.txt' INTO TABLE t2 FIELDS ESCAPED BY '' TERMINATED BY ' '; 458SELECT 'before' AS t, id, val1, hex(val1) FROM t1 UNION 459SELECT 'after' AS t, id, val1, hex(val1) FROM t2 ORDER BY id,t DESC; 460t id val1 hex(val1) 461before 3 \tx 5C7478 462after 3 \tx 5C7478 463before 4 \r 5C72 464after 4 \r 5C72 465before 5 tab 09746162 466after 5 tab 09746162 467TRUNCATE t2; 468SET sql_mode = 'NO_BACKSLASH_ESCAPES'; 469SELECT LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt"); 470LOAD_FILE("MYSQLTEST_VARDIR/tmp/bug37114.txt") 4713 \tx 4724 \r 4735 tab 474 475set session sql_mode=@OLD_SQL_MODE; 476DROP TABLE t1,t2; 477# 478# Bug #51893: crash with certain characters given to load_file 479# function on windows 480# 481select load_file(0x0A9FB76C661B409C4BEC88098C5DD71B1072F9691F2E827D7EC8F092B299868A3CE196C04F0FB18CAB4E1557EB72331D812379DE7A75CA21C32E7C722C59E5CC33EF262EF04187B0F0EE756FA984DF2EAD37B1E4ADB064C3C5038F2E3B2D661B1C1150AAEB5425512E14D7506166D92D4533872E662F4B2D1428AAB5CCA72E75AA2EF325E196A5A02E2E8278873C64375845994B0F39BE2FF7B478332A7B0AA5E48877C47B6F513E997848AF8CCB8A899F3393AB35333CF0871E36698193862D486B4B9078B70C0A0A507B8A250F3F876F5A067632D5E65193E4445A1EC3A2C9B4C6F07AC334F0F62BC33357CB502E9B1C19D2398B6972AEC2EF21630F8C9134C4F7DD662D8AD7BDC9E19C46720F334B66C22D4BF32ED275144E20E7669FFCF6FC143667C9F02A577F32960FA9F2371BE1FA90E49CBC69C01531F140556854D588DD0E55E1307D78CA38E975CD999F9AEA604266329EE62BFB5ADDA67F549E211ECFBA906C60063696352ABB82AA782D25B17E872EA587871F450446DB1BAE0123D20404A8F2D2698B371002E986C8FCB969A99FF0E150A2709E2ED7633D02ADA87D5B3C9487D27B2BD9D21E2EC3215DCC3CDCD884371281B95A2E9987AAF82EB499C058D9C3E7DC1B66635F60DB121C72F929622DD47B6B2E69F59FF2AE6B63CC2EC60FFBA20EA50569DBAB5DAEFAEB4F03966C9637AB55662EDD28439155A82D053A5299448EDB2E7BEB0F62889E2F84E6C7F34B3212C9AAC32D521D5AB8480993F1906D5450FAB342A0FA6ED223E178BAC036B81E15783604C32A961EA1EF20BE2EBB93D34ED37BC03142B7583303E4557E48551E4BD7CBDDEA146D5485A5D212C35189F0BD6497E66912D2780A59A53B532E12C0A5ED1EC0445D96E8F2DD825221CFE4A65A87AA21DC8750481B9849DD81694C3357A0ED9B78D608D8EDDE28FAFBEC17844DE5709F41E121838DB55639D77E32A259A416D7013B2EB1259FDE1B498CBB9CAEE1D601DF3C915EA91C69B44E6B72062F5F4B3C73F06F2D5AD185E1692E2E0A01E7DD5133693681C52EE13B2BE42D03BDCF48E4E133CF06662339B778E1C3034F9939A433E157449172F7969ACCE1F5D2F65A4E09E4A5D5611EBEDDDBDB0C0C0A); 482load_file(0x0A9FB76C661B409C4BEC88098C5DD71B1072F9691F2E827D7EC8F092B299868A3CE196C04F0FB18CAB4E1557EB72331D812379DE7A75CA21C32E7C722C59E5CC33EF262EF04187B0F0EE756FA984DF2EAD37B1E4ADB064C3C5038F2E3B2D661B1C1150AAEB5425512E14D7506166D92D4533872E662F4B2D142 483NULL 484End of 5.0 tests 485CREATE TABLE t1 (a int); 486INSERT INTO t1 VALUES (1); 487SET NAMES latin1; 488SET character_set_filesystem=filename; 489select @@character_set_filesystem; 490@@character_set_filesystem 491filename 492SELECT * INTO OUTFILE 't-1' FROM t1; 493DELETE FROM t1; 494LOAD DATA INFILE 't-1' INTO TABLE t1; 495SELECT * FROM t1; 496a 4971 498DELETE FROM t1; 499SET character_set_filesystem=latin1; 500select @@character_set_filesystem; 501@@character_set_filesystem 502latin1 503LOAD DATA INFILE 't@002d1' INTO TABLE t1; 504SELECT * FROM t1; 505a 5061 507DROP TABLE t1; 508SET character_set_filesystem=default; 509select @@character_set_filesystem; 510@@character_set_filesystem 511binary 512# 513# Bug #51850: crash/memory overlap when using load data infile and set 514# col equal to itself! 515# 516CREATE TABLE t1(col0 LONGBLOB); 517SELECT 'test' INTO OUTFILE 't1.txt'; 518LOAD DATA INFILE 't1.txt' IGNORE INTO TABLE t1 SET col0=col0; 519SELECT * FROM t1; 520col0 521test 522DROP TABLE t1; 523# 524# Bug #52512 : Assertion `! is_set()' in 525# Diagnostics_area::set_ok_status on LOAD DATA 526# 527connect con1,localhost,root,,test; 528CREATE TABLE t1 (id INT NOT NULL); 529LOAD DATA LOCAL INFILE 'tb.txt' INTO TABLE t1; 530connection default; 531disconnect con1; 532connect con1,localhost,root,,test; 533DROP TABLE t1; 534connection default; 535disconnect con1; 536# 537# Bug#11765139 58069: LOAD DATA INFILE: VALGRIND REPORTS INVALID MEMORY READS AND WRITES WITH U 538# 539CREATE TABLE t1(f1 INT); 540SELECT 0xE1BB30 INTO OUTFILE 't1.dat'; 541LOAD DATA INFILE 't1.dat' IGNORE INTO TABLE t1 CHARACTER SET utf8; 542DROP TABLE t1; 543# 544# Bug#11765141 - 58072: LOAD DATA INFILE: LEAKS IO CACHE MEMORY 545# WHEN ERROR OCCURS 546# 547SELECT '1\n' INTO DUMPFILE 'MYSQLTEST_VARDIR/tmp/bug11735141.txt'; 548create table t1(a point); 549LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug11735141.txt' INTO TABLE t1; 550ERROR 22003: Cannot get geometry object from data you send to the GEOMETRY field 551drop table t1; 552End of 5.1 tests 553# 554# Bug#11759519 INFINITE HANG WITH 100% CPU USAGE WITH LOAD DATA LOCAL AND IMPORT ERRORS 555# 556SET @old_mode= @@sql_mode; 557CREATE TABLE t1 (fld1 INT); 558SET sql_mode='strict_all_tables'; 559# Without fix, load data hangs forever. 560LOAD DATA LOCAL INFILE 'MYSQLTEST_VARDIR/mysql' REPLACE INTO TABLE t1 561FIELDS TERMINATED BY 't' LINES TERMINATED BY ''; 562Got one of the listed errors 563SET @@sql_mode= @old_mode; 564DROP TABLE t1; 565# 566# MDEV-11079 Regression: LOAD DATA INFILE lost BLOB support using utf8 load files 567# 568CREATE TABLE t1 (a mediumblob NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; 569LOAD DATA INFILE '../../std_data/loaddata/mdev-11079.txt' INTO TABLE t1 CHARSET utf8 FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n'; 570SELECT HEX(a) FROM t1; 571HEX(a) 57225AAABAC 573DROP TABLE t1; 574# 575# MDEV-11631 LOAD DATA INFILE fails to load data with an escape character followed by a multi-byte character 576# 577CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8); 578LOAD DATA INFILE '../../std_data/loaddata/mdev-11631.txt' INTO TABLE t1 CHARACTER SET utf8; 579SELECT HEX(a) FROM t1; 580HEX(a) 581C3A4 582DROP TABLE t1; 583# 584# MDEV-15744: Assertion `derived->table' failed in mysql_derived_merge_for_insert 585# 586create table t1 (a int, b int); 587CREATE OR REPLACE VIEW t2 AS SELECT * FROM t1; 588CREATE VIEW v2 AS SELECT * FROM t2; 589LOAD DATA INFILE '../../std_data/loaddata7.dat' INTO TABLE v2 590FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'; 591select * from v2; 592a b 5932 2 5943 3 5954 4 5965 5 5976 6 598select * from t2; 599a b 6002 2 6013 3 6024 4 6035 5 6046 6 605DROP VIEW IF EXISTS v2,t2; 606DROP TABLE IF EXISTS t1; 607# 608# MDEV-15950: LOAD DATA INTO compex_view crashed 609# 610create table t1 (a int, b int); 611create table t0 (x int, y int); 612CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1,t0; 613CREATE VIEW v2 AS SELECT * FROM v1; 614LOAD DATA INFILE '../../std_data/loaddata7.dat' INTO TABLE v1 615FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'; 616ERROR HY000: Incorrect usage of Multi-table VIEW and LOAD 617LOAD DATA INFILE '../../std_data/loaddata7.dat' INTO TABLE v2 618FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'; 619ERROR HY000: Incorrect usage of Multi-table VIEW and LOAD 620DROP VIEW IF EXISTS v2,v1; 621DROP TABLE IF EXISTS t1,t0; 622CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), UNIQUE(b)); 623INSERT INTO t1 VALUES (1,1); 624CREATE TABLE t2 (c INT); 625CREATE VIEW v AS SELECT t1.* FROM t1 JOIN t2; 626SELECT a, b INTO OUTFILE '15645.data' FROM t1; 627LOAD DATA INFILE '15645.data' IGNORE INTO TABLE v (a,b); 628ERROR HY000: Incorrect usage of Multi-table VIEW and LOAD 629LOAD DATA INFILE '15645.data' REPLACE INTO TABLE v (a,b); 630ERROR HY000: Incorrect usage of Multi-table VIEW and LOAD 631drop table t1,t2; 632drop view v; 633# 634# MDEV-15497 Wrong empty value in a GEOMETRY column on LOAD DATA 635# 636SET sql_mode=''; 637CREATE TABLE t1 (a CHAR(1), b CHAR(1) NOT NULL); 638LOAD DATA INFILE '../../std_data/loaddata/mdev-15497.txt' INTO TABLE t1; 639Warnings: 640Warning 1261 Row 1 doesn't contain data for all columns 641SELECT * FROM t1; 642a b 6431 644TRUNCATE TABLE t1; 645LOAD DATA INFILE '../../std_data/loaddata/mdev-15497.txt' INTO TABLE t1 FIELDS TERMINATED BY ''; 646Warnings: 647Warning 1261 Row 1 doesn't contain data for all columns 648SELECT * FROM t1; 649a b 6501 651DROP TABLE t1; 652CREATE TABLE t1 (a CHAR(1), b CHAR(1)); 653LOAD DATA INFILE '../../std_data/loaddata/mdev-15497.txt' INTO TABLE t1; 654Warnings: 655Warning 1261 Row 1 doesn't contain data for all columns 656SELECT * FROM t1; 657a b 6581 NULL 659TRUNCATE TABLE t1; 660LOAD DATA INFILE '../../std_data/loaddata/mdev-15497.txt' INTO TABLE t1 FIELDS TERMINATED BY ''; 661Warnings: 662Warning 1261 Row 1 doesn't contain data for all columns 663SELECT * FROM t1; 664a b 6651 666DROP TABLE t1; 667# 668# MDEV-15597 Add class Load_data_outvar and avoid using Item::STRING_ITEM for Item_user_var_as_out_param detection 669# 670SET sql_mode=NO_AUTO_VALUE_ON_ZERO; 671CREATE TABLE t1 (id integer not null auto_increment primary key); 672LOAD DATA INFILE '../../std_data/loaddata/nl.txt' INTO TABLE t1 FIELDS TERMINATED BY ''; 673Warnings: 674Warning 1261 Row 1 doesn't contain data for all columns 675SELECT * FROM t1; 676id 6770 678DROP TABLE t1; 679SET sql_mode=''; 680CREATE TABLE t1 (id integer not null auto_increment primary key); 681LOAD DATA INFILE '../../std_data/loaddata/nl.txt' INTO TABLE t1 FIELDS TERMINATED BY ''; 682Warnings: 683Warning 1261 Row 1 doesn't contain data for all columns 684SELECT * FROM t1; 685id 6861 687DROP TABLE t1; 688