1 DROP TABLE IF EXISTS t1, t2;
3 # Bug#31663 FIELDS TERMINATED BY special character
5 CREATE TABLE t1 (i1 int, i2 int, c1 VARCHAR(256), c2 VARCHAR(256));
6 INSERT INTO t1 VALUES (101, 202, '-r-', '=raker=');
7 # FIELDS TERMINATED BY 'raker', warning:
8 SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' FIELDS TERMINATED BY 'raker' FROM t1;
10 Warning 1475 First character of the FIELDS TERMINATED string is ambiguous; please use non-optional and non-empty FIELDS ENCLOSED BY
11 SELECT LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt');
12 LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt')
13 101raker202raker-r-raker=raker=
15 CREATE TABLE t2 SELECT * FROM t1;
16 LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 FIELDS TERMINATED BY 'raker';
18 Warning 1262 Row 1 was truncated; it contained more data than there were input columns
24 # Only numeric fields, FIELDS TERMINATED BY 'r', no warnings:
25 SELECT i1, i2 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' FIELDS TERMINATED BY 'r' FROM t1;
26 SELECT LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt');
27 LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt')
30 CREATE TABLE t2 SELECT i1, i2 FROM t1;
31 LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 FIELDS TERMINATED BY 'r';
32 SELECT i1, i2 FROM t2;
37 # FIELDS TERMINATED BY '0', warning:
38 SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' FIELDS TERMINATED BY '0' FROM t1;
40 Warning 1475 First character of the FIELDS TERMINATED string is ambiguous; please use non-optional and non-empty FIELDS ENCLOSED BY
41 SELECT LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt');
42 LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt')
45 CREATE TABLE t2 SELECT * FROM t1;
46 LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 FIELDS TERMINATED BY '0';
48 Warning 1262 Row 1 was truncated; it contained more data than there were input columns
54 # FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0', warning:
55 SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0' FROM t1;
57 Warning 1475 First character of the FIELDS TERMINATED string is ambiguous; please use non-optional and non-empty FIELDS ENCLOSED BY
58 SELECT LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt');
59 LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt')
60 10102020"-r-"0"=raker="
62 CREATE TABLE t2 SELECT * FROM t1;
63 LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0';
65 Warning 1262 Row 1 was truncated; it contained more data than there were input columns
71 # Only string fields, FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0', no warnings:
72 SELECT c1, c2 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0' FROM t1;
73 SELECT LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt');
74 LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug31663.txt')
77 CREATE TABLE t2 SELECT c1, c2 FROM t1;
78 LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0';
79 SELECT c1, c2 FROM t2;
86 # Bug#32533: SELECT INTO OUTFILE never escapes multibyte character
88 CREATE TABLE t1 (c1 VARCHAR(256));
89 INSERT INTO t1 VALUES (0xC3);
90 SELECT HEX(c1) FROM t1;
93 SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug32533.txt' FIELDS ENCLOSED BY 0xC3 FROM t1;
95 Warning 1638 Non-ASCII separator arguments are not fully supported
97 SELECT HEX(LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug32533.txt'));
98 HEX(LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug32533.txt'))
100 LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug32533.txt' INTO TABLE t1 FIELDS ENCLOSED BY 0xC3;
102 Warning 1638 Non-ASCII separator arguments are not fully supported
103 SELECT HEX(c1) FROM t1;
109 # Bug #30946: mysqldump silently ignores --default-character-set
110 # when used with --tab
112 # Also see mysqldump.test
115 CREATE TABLE t1 (a INT, b CHAR(10) CHARSET koi8r, c CHAR(10) CHARSET latin1);
116 CREATE TABLE t2 LIKE t1;
117 INSERT INTO t1 VALUES (1, 'ABC-АБВ', 'DEF-ÂÃÄ'), (2, NULL, NULL);
118 # Error on multi-character ENCLOSED/ESCAPED BY
119 SELECT * FROM t1 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' FIELDS ENCLOSED BY '12345';
120 ERROR 42000: Field separator argument is not what is expected; check the manual
121 SELECT * FROM t1 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' FIELDS ESCAPED BY '12345';
122 ERROR 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
125 SELECT * FROM t1 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' FIELDS ENCLOSED BY 'ъ';
127 Warning 1638 Non-ASCII separator arguments are not fully supported
128 LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' INTO TABLE t2 CHARACTER SET binary FIELDS ENCLOSED BY 'ъ';
129 ERROR 42000: Field separator argument is not what is expected; check the manual
130 SELECT * FROM t1 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' FIELDS ESCAPED BY 'ъ';
132 Warning 1638 Non-ASCII separator arguments are not fully supported
133 LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' INTO TABLE t2 CHARACTER SET binary FIELDS ESCAPED BY 'ъ';
134 ERROR 42000: Field separator argument is not what is expected; check the manual
135 SELECT * FROM t1 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' FIELDS TERMINATED BY 'ъ';
137 Warning 1638 Non-ASCII separator arguments are not fully supported
138 ##################################################
141 ##################################################
143 LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' INTO TABLE t2 CHARACTER SET binary FIELDS TERMINATED BY 'ъ';
145 Warning 1638 Non-ASCII separator arguments are not fully supported
146 Warning 1265 Data truncated for column 'a' at row 1
147 Warning 1261 Row 1 doesn't contain data for all columns
148 Warning 1261 Row 1 doesn't contain data for all columns
149 Warning 1265 Data truncated for column 'a' at row 2
150 Warning 1261 Row 2 doesn't contain data for all columns
151 Warning 1261 Row 2 doesn't contain data for all columns
152 SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c;
157 SELECT * FROM t1 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' LINES STARTING BY 'ъ';
159 Warning 1638 Non-ASCII separator arguments are not fully supported
160 ##################################################
163 ##################################################
165 LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' INTO TABLE t2 CHARACTER SET binary LINES STARTING BY 'ъ';
167 Warning 1638 Non-ASCII separator arguments are not fully supported
168 SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c;
172 SELECT * FROM t1 INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' LINES TERMINATED BY 'ъ';
174 Warning 1638 Non-ASCII separator arguments are not fully supported
175 ##################################################
176 1 ABC-áâ÷ DEF-ÂÃÄÑŠ2 \N \NÑŠ##################################################
178 LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' INTO TABLE t2 CHARACTER SET binary LINES TERMINATED BY 'ъ';
180 Warning 1638 Non-ASCII separator arguments are not fully supported
181 SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c;
186 # Default (binary) charset:
187 SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' FROM t1;
188 ##################################################
191 ##################################################
193 LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' INTO TABLE t2 CHARACTER SET binary;
194 SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c;
198 # latin1 charset (INTO OUTFILE warning is expected):
199 SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' CHARACTER SET latin1 FROM t1;
201 Warning 1366 Incorrect string value: '\xE1\xE2\xF7' for column 'b' at row 1
202 ##################################################
205 ##################################################
207 LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' INTO TABLE t2 CHARACTER SET latin1 ;
208 SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c;
213 # KOI8-R charset (INTO OUTFILE warning is expected):
214 SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' CHARACTER SET koi8r FROM t1;
216 Warning 1366 Incorrect string value: '\xC2\xC3\xC4' for column 'c' at row 1
217 ##################################################
220 ##################################################
222 LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' INTO TABLE t2 CHARACTER SET koi8r;
223 SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c;
229 SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' CHARACTER SET utf8 FROM t1;
230 ##################################################
233 ##################################################
235 LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/t1.txt' INTO TABLE t2 CHARACTER SET utf8;
236 SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c;
243 # Bug #53088: mysqldump with -T & --default-character-set set
244 # truncates text/blob to 766 chars
246 # Also see mysqldump.test
248 CREATE TABLE t1 (a BLOB) CHARSET latin1;
249 CREATE TABLE t2 LIKE t1;
250 INSERT INTO t1 VALUES (REPEAT('.', 800));
251 SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug53088.txt' CHARACTER SET latin1 FROM t1;
252 # should be greater than 800
253 SELECT LENGTH(LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug53088.txt'));
254 LENGTH(LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug53088.txt'))
256 LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug53088.txt' INTO TABLE t2;
258 SELECT LENGTH(a) FROM t2;