1DROP TABLE IF EXISTS t1, t2; 2# 3# Bug#31663 FIELDS TERMINATED BY special character 4# 5CREATE TABLE t1 (i1 int, i2 int, c1 VARCHAR(256), c2 VARCHAR(256)); 6INSERT INTO t1 VALUES (101, 202, '-r-', '=raker='); 7# FIELDS TERMINATED BY 'raker', warning: 8SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' FIELDS TERMINATED BY 'raker' FROM t1; 9Warnings: 10Warning 1475 First character of the FIELDS TERMINATED string is ambiguous; please use non-optional and non-empty FIELDS ENCLOSED BY 11SELECT LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt'); 12LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt') 13101raker202raker-r-raker=raker= 14 15CREATE TABLE t2 SELECT * FROM t1; 16LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 FIELDS TERMINATED BY 'raker'; 17Warnings: 18Warning 1262 Row 1 was truncated; it contained more data than there were input columns 19SELECT * FROM t2; 20i1 i2 c1 c2 21101 202 -r- =raker= 22101 202 -r- = 23DROP TABLE t2; 24# Only numeric fields, FIELDS TERMINATED BY 'r', no warnings: 25SELECT i1, i2 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' FIELDS TERMINATED BY 'r' FROM t1; 26SELECT LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt'); 27LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt') 28101r202 29 30CREATE TABLE t2 SELECT i1, i2 FROM t1; 31LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 FIELDS TERMINATED BY 'r'; 32SELECT i1, i2 FROM t2; 33i1 i2 34101 202 35101 202 36DROP TABLE t2; 37# FIELDS TERMINATED BY '0', warning: 38SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' FIELDS TERMINATED BY '0' FROM t1; 39Warnings: 40Warning 1475 First character of the FIELDS TERMINATED string is ambiguous; please use non-optional and non-empty FIELDS ENCLOSED BY 41SELECT LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt'); 42LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt') 4310102020-r-0=raker= 44 45CREATE TABLE t2 SELECT * FROM t1; 46LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 FIELDS TERMINATED BY '0'; 47Warnings: 48Warning 1262 Row 1 was truncated; it contained more data than there were input columns 49SELECT * FROM t2; 50i1 i2 c1 c2 51101 202 -r- =raker= 521 1 2 2 53DROP TABLE t2; 54# FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0', warning: 55SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0' FROM t1; 56Warnings: 57Warning 1475 First character of the FIELDS TERMINATED string is ambiguous; please use non-optional and non-empty FIELDS ENCLOSED BY 58SELECT LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt'); 59LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt') 6010102020"-r-"0"=raker=" 61 62CREATE TABLE t2 SELECT * FROM t1; 63LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0'; 64Warnings: 65Warning 1262 Row 1 was truncated; it contained more data than there were input columns 66SELECT * FROM t2; 67i1 i2 c1 c2 68101 202 -r- =raker= 691 1 2 2 70DROP TABLE t2; 71# Only string fields, FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0', no warnings: 72SELECT c1, c2 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0' FROM t1; 73SELECT LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt'); 74LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt') 75"-r-"0"=raker=" 76 77CREATE TABLE t2 SELECT c1, c2 FROM t1; 78LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0'; 79SELECT c1, c2 FROM t2; 80c1 c2 81-r- =raker= 82-r- =raker= 83DROP TABLE t2; 84DROP TABLE t1; 85# 86# Bug#32533: SELECT INTO OUTFILE never escapes multibyte character 87# 88CREATE TABLE t1 (c1 VARCHAR(256)); 89INSERT INTO t1 VALUES (0xC3); 90SELECT HEX(c1) FROM t1; 91HEX(c1) 92C3 93SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug32533.txt' FIELDS ENCLOSED BY 0xC3 FROM t1; 94Warnings: 95Warning 1638 Non-ASCII separator arguments are not fully supported 96TRUNCATE t1; 97SELECT HEX(LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug32533.txt')); 98HEX(LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug32533.txt')) 99C35CC3C30A 100LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug32533.txt' INTO TABLE t1 FIELDS ENCLOSED BY 0xC3; 101Warnings: 102Warning 1638 Non-ASCII separator arguments are not fully supported 103SELECT HEX(c1) FROM t1; 104HEX(c1) 105C3 106DROP TABLE t1; 107# End of 5.0 tests. 108# 109# Bug #30946: mysqldump silently ignores --default-character-set 110# when used with --tab 111# 112# Also see mysqldump.test 113# 114SET NAMES utf8; 115CREATE TABLE t1 (a INT, b CHAR(10) CHARSET koi8r, c CHAR(10) CHARSET latin1); 116CREATE TABLE t2 LIKE t1; 117INSERT INTO t1 VALUES (1, 'ABC-АБВ', 'DEF-ÂÃÄ'), (2, NULL, NULL); 118# Error on multi-character ENCLOSED/ESCAPED BY 119SELECT * FROM t1 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' FIELDS ENCLOSED BY '12345'; 120ERROR 42000: Field separator argument is not what is expected; check the manual 121SELECT * FROM t1 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' FIELDS ESCAPED BY '12345'; 122ERROR 42000: Field separator argument is not what is expected; check the manual 123# "Not implemented" warning on multibyte ENCLOSED/ESCAPED BY character, 124# LOAD DATA rises error or has unpredictable result -- to be fixed later 125SELECT * FROM t1 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' FIELDS ENCLOSED BY 'ъ'; 126Warnings: 127Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 128Warning 1638 Non-ASCII separator arguments are not fully supported 129LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' INTO TABLE t2 CHARACTER SET binary FIELDS ENCLOSED BY 'ъ'; 130ERROR 42000: Field separator argument is not what is expected; check the manual 131SELECT * FROM t1 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' FIELDS ESCAPED BY 'ъ'; 132Warnings: 133Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 134Warning 1638 Non-ASCII separator arguments are not fully supported 135LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' INTO TABLE t2 CHARACTER SET binary FIELDS ESCAPED BY 'ъ'; 136ERROR 42000: Field separator argument is not what is expected; check the manual 137SELECT * FROM t1 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' FIELDS TERMINATED BY 'ъ'; 138Warnings: 139Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 140Warning 1638 Non-ASCII separator arguments are not fully supported 141################################################## 1421ъABC-���ъDEF-��� 1432ъ\Nъ\N 144################################################## 145TRUNCATE t2; 146LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' INTO TABLE t2 CHARACTER SET binary FIELDS TERMINATED BY 'ъ'; 147Warnings: 148Warning 1638 Non-ASCII separator arguments are not fully supported 149SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c; 150a b c 1511 ABC-АБВ DEF-ÂÃÄ 1522 NULL NULL 153SELECT * FROM t1; 154a b c 1551 ABC-АБВ DEF-ÂÃÄ 1562 NULL NULL 157SELECT * FROM t2; 158a b c 1591 ABC-АБВ DEF-ÂÃÄ 1602 NULL NULL 161SELECT * FROM t1 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' LINES STARTING BY 'ъ'; 162Warnings: 163Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 164Warning 1638 Non-ASCII separator arguments are not fully supported 165################################################## 166ъ1 ABC-��� DEF-��� 167ъ2 \N \N 168################################################## 169TRUNCATE t2; 170LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' INTO TABLE t2 CHARACTER SET binary LINES STARTING BY 'ъ'; 171Warnings: 172Warning 1638 Non-ASCII separator arguments are not fully supported 173SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c; 174a b c 1751 ABC-АБВ DEF-ÂÃÄ 1762 NULL NULL 177SELECT * FROM t1 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' LINES TERMINATED BY 'ъ'; 178Warnings: 179Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 180Warning 1638 Non-ASCII separator arguments are not fully supported 181################################################## 1821 ABC-��� DEF-���ъ2 \N \Nъ################################################## 183TRUNCATE t2; 184LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' INTO TABLE t2 CHARACTER SET binary LINES TERMINATED BY 'ъ'; 185Warnings: 186Warning 1638 Non-ASCII separator arguments are not fully supported 187SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c; 188a b c 1891 ABC-АБВ DEF-ÂÃÄ 1902 NULL NULL 191SELECT * FROM t1; 192a b c 1931 ABC-АБВ DEF-ÂÃÄ 1942 NULL NULL 195SELECT * FROM t2; 196a b c 1971 ABC-АБВ DEF-ÂÃÄ 1982 NULL NULL 199# Default (binary) charset: 200SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' FROM t1; 201################################################## 2021 ABC-��� DEF-��� 2032 \N \N 204################################################## 205TRUNCATE t2; 206LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' INTO TABLE t2 CHARACTER SET binary; 207SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c; 208a b c 2091 ABC-АБВ DEF-ÂÃÄ 2102 NULL NULL 211# latin1 charset (INTO OUTFILE warning is expected): 212SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' CHARACTER SET latin1 FROM t1; 213Warnings: 214Warning 1366 Data truncated for column 'b' at row 1 215################################################## 2161 ABC-??? DEF-��� 2172 \N \N 218################################################## 219TRUNCATE t2; 220LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' INTO TABLE t2 CHARACTER SET latin1 ; 221SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c; 222a b c 2231 ABC-??? DEF-ÂÃÄ 2241 ABC-АБВ DEF-ÂÃÄ 2252 NULL NULL 226# KOI8-R charset (INTO OUTFILE warning is expected): 227SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' CHARACTER SET koi8r FROM t1; 228Warnings: 229Warning 1366 Data truncated for column 'c' at row 1 230################################################## 2311 ABC-��� DEF-??? 2322 \N \N 233################################################## 234TRUNCATE t2; 235LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' INTO TABLE t2 CHARACTER SET koi8r; 236SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c; 237a b c 2381 ABC-АБВ DEF-??? 2391 ABC-АБВ DEF-ÂÃÄ 2402 NULL NULL 241# UTF-8 charset: 242SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' CHARACTER SET utf8 FROM t1; 243################################################## 2441 ABC-АБВ DEF-ÂÃÄ 2452 \N \N 246################################################## 247TRUNCATE t2; 248LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' INTO TABLE t2 CHARACTER SET utf8; 249SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c; 250a b c 2511 ABC-АБВ DEF-ÂÃÄ 2522 NULL NULL 253SET NAMES default; 254DROP TABLE t1, t2; 255# 256# Bug #53088: mysqldump with -T & --default-character-set set 257# truncates text/blob to 766 chars 258# 259# Also see mysqldump.test 260# 261CREATE TABLE t1 (a BLOB) CHARSET latin1; 262CREATE TABLE t2 LIKE t1; 263INSERT INTO t1 VALUES (REPEAT('.', 800)); 264SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug53088.txt' CHARACTER SET latin1 FROM t1; 265# should be greater than 800 266SELECT LENGTH(LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug53088.txt')); 267LENGTH(LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug53088.txt')) 268801 269LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug53088.txt' INTO TABLE t2; 270# should be 800 271SELECT LENGTH(a) FROM t2; 272LENGTH(a) 273800 274DROP TABLE t1, t2; 275# End of 5.1 tests. 276