1--disable_warnings
2DROP TABLE IF EXISTS t1, t2;
3--enable_warnings
4
5--echo #
6--echo # Bug#31663 FIELDS TERMINATED BY special character
7--echo #
8
9CREATE TABLE t1 (i1 int, i2 int, c1 VARCHAR(256), c2 VARCHAR(256));
10INSERT INTO t1 VALUES (101, 202, '-r-', '=raker=');
11
12--let $fields=*
13--let $clauses=FIELDS TERMINATED BY 'raker'
14--echo # $clauses, warning:
15
16--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
17--eval SELECT $fields INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' $clauses FROM t1
18--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
19--eval SELECT LOAD_FILE('$MYSQLTEST_VARDIR/tmp/bug31663.txt')
20--eval CREATE TABLE t2 SELECT $fields FROM t1
21--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
22--eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 $clauses
23--eval SELECT $fields FROM t2
24--remove_file $MYSQLTEST_VARDIR/tmp/bug31663.txt
25DROP TABLE t2;
26
27--let $fields=i1, i2
28--let $clauses=FIELDS TERMINATED BY 'r'
29--echo # Only numeric fields, $clauses, no warnings:
30
31--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
32--eval SELECT $fields INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' $clauses FROM t1
33--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
34--eval SELECT LOAD_FILE('$MYSQLTEST_VARDIR/tmp/bug31663.txt')
35--eval CREATE TABLE t2 SELECT $fields FROM t1
36--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
37--eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 $clauses
38--eval SELECT $fields FROM t2
39--remove_file $MYSQLTEST_VARDIR/tmp/bug31663.txt
40DROP TABLE t2;
41
42--let $fields=*
43--let $clauses=FIELDS TERMINATED BY '0'
44--echo # $clauses, warning:
45
46--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
47--eval SELECT $fields INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' $clauses FROM t1
48--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
49--eval SELECT LOAD_FILE('$MYSQLTEST_VARDIR/tmp/bug31663.txt')
50--eval CREATE TABLE t2 SELECT $fields FROM t1
51--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
52--eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 $clauses
53--eval SELECT $fields FROM t2
54--remove_file $MYSQLTEST_VARDIR/tmp/bug31663.txt
55DROP TABLE t2;
56
57--let $fields=*
58--let $clauses=FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0'
59--echo # $clauses, warning:
60
61--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
62--eval SELECT $fields INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' $clauses FROM t1
63--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
64--eval SELECT LOAD_FILE('$MYSQLTEST_VARDIR/tmp/bug31663.txt')
65--eval CREATE TABLE t2 SELECT $fields FROM t1
66--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
67--eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 $clauses
68--eval SELECT $fields FROM t2
69--remove_file $MYSQLTEST_VARDIR/tmp/bug31663.txt
70DROP TABLE t2;
71
72--let $fields=c1, c2
73--let $clauses=FIELDS OPTIONALLY ENCLOSED BY '"' TERMINATED BY '0'
74--echo # Only string fields, $clauses, no warnings:
75
76--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
77--eval SELECT $fields INTO OUTFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' $clauses FROM t1
78--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
79--eval SELECT LOAD_FILE('$MYSQLTEST_VARDIR/tmp/bug31663.txt')
80--eval CREATE TABLE t2 SELECT $fields FROM t1
81--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
82--eval LOAD DATA INFILE '$MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 $clauses
83--eval SELECT $fields FROM t2
84--remove_file $MYSQLTEST_VARDIR/tmp/bug31663.txt
85DROP TABLE t2;
86
87DROP TABLE t1;
88
89--echo #
90--echo # Bug#32533: SELECT INTO OUTFILE never escapes multibyte character
91--echo #
92
93CREATE TABLE t1 (c1 VARCHAR(256));
94INSERT INTO t1 VALUES (0xC3);
95SELECT HEX(c1) FROM t1;
96
97--let $file=$MYSQLTEST_VARDIR/tmp/bug32533.txt
98
99--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
100--eval SELECT * INTO OUTFILE '$file' FIELDS ENCLOSED BY 0xC3 FROM t1
101TRUNCATE t1;
102
103--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
104--eval SELECT HEX(LOAD_FILE('$file'))
105
106--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
107--eval LOAD DATA INFILE '$file' INTO TABLE t1 FIELDS ENCLOSED BY 0xC3
108SELECT HEX(c1) FROM t1;
109
110--remove_file $file
111DROP TABLE t1;
112
113--echo # End of 5.0 tests.
114
115###########################################################################
116
117--echo #
118--echo # Bug #30946: mysqldump silently ignores --default-character-set
119--echo #             when used with --tab
120--echo #
121--echo # Also see mysqldump.test
122--echo #
123
124SET NAMES utf8;
125CREATE TABLE t1 (a INT, b CHAR(10) CHARSET koi8r, c CHAR(10) CHARSET latin1);
126CREATE TABLE t2 LIKE t1;
127INSERT INTO t1 VALUES (1, 'ABC-АБВ', 'DEF-ÂÃÄ'), (2, NULL, NULL);
128
129--let $file=$MYSQLTEST_VARDIR/tmp/t1.txt
130
131
132--echo # Error on multi-character ENCLOSED/ESCAPED BY
133
134--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
135--error 1083
136--eval SELECT * FROM t1 INTO OUTFILE '$file' FIELDS ENCLOSED BY '12345'
137--remove_file $file
138
139--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
140--error 1083
141--eval SELECT * FROM t1 INTO OUTFILE '$file' FIELDS ESCAPED BY '12345'
142--remove_file $file
143
144
145--echo # "Not implemented" warning on multibyte  ENCLOSED/ESCAPED BY character,
146--echo # LOAD DATA rises error or has unpredictable result -- to be fixed later
147
148--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
149--eval SELECT * FROM t1 INTO OUTFILE '$file' FIELDS ENCLOSED BY 'ъ'
150--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
151--error 1083 # backward compatibility
152--eval LOAD DATA INFILE '$file' INTO TABLE t2 CHARACTER SET binary FIELDS ENCLOSED BY 'ъ'
153--remove_file $file
154
155--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
156--eval SELECT * FROM t1 INTO OUTFILE '$file' FIELDS ESCAPED BY 'ъ'
157--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
158--error 1083 # backward compatibility
159--eval LOAD DATA INFILE '$file' INTO TABLE t2 CHARACTER SET binary FIELDS ESCAPED BY 'ъ'
160--remove_file $file
161
162--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
163--eval SELECT * FROM t1 INTO OUTFILE '$file' FIELDS TERMINATED BY 'ъ'
164--echo ##################################################
165--cat_file $file
166--echo ##################################################
167TRUNCATE t2;
168--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
169--eval LOAD DATA INFILE '$file' INTO TABLE t2 CHARACTER SET binary FIELDS TERMINATED BY 'ъ'
170--remove_file $file
171SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c;
172SELECT * FROM t1;
173SELECT * FROM t2;
174
175--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
176--eval SELECT * FROM t1 INTO OUTFILE '$file' LINES STARTING BY 'ъ'
177--echo ##################################################
178--cat_file $file
179--echo ##################################################
180TRUNCATE t2;
181--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
182--eval LOAD DATA INFILE '$file' INTO TABLE t2 CHARACTER SET binary LINES STARTING BY 'ъ'
183--remove_file $file
184SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c;
185
186--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
187--eval SELECT * FROM t1 INTO OUTFILE '$file' LINES TERMINATED BY 'ъ'
188--echo ##################################################
189--cat_file $file
190--echo ##################################################
191TRUNCATE t2;
192--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
193--eval LOAD DATA INFILE '$file' INTO TABLE t2 CHARACTER SET binary LINES TERMINATED BY 'ъ'
194--remove_file $file
195SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c;
196SELECT * FROM t1;
197SELECT * FROM t2;
198
199--echo # Default (binary) charset:
200
201--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
202--eval SELECT * INTO OUTFILE '$file' FROM t1
203--echo ##################################################
204--cat_file $file
205--echo ##################################################
206TRUNCATE t2;
207--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
208--eval LOAD DATA INFILE '$file' INTO TABLE t2 CHARACTER SET binary
209--remove_file $file
210SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c;
211
212
213--echo # latin1 charset (INTO OUTFILE warning is expected):
214
215--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
216--eval SELECT * INTO OUTFILE '$file' CHARACTER SET latin1 FROM t1
217--echo ##################################################
218--cat_file $file
219--echo ##################################################
220TRUNCATE t2;
221--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
222--eval LOAD DATA INFILE '$file' INTO TABLE t2 CHARACTER SET latin1
223--remove_file $file
224SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c;
225
226
227--echo # KOI8-R charset (INTO OUTFILE warning is expected):
228
229--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
230--eval SELECT * INTO OUTFILE '$file' CHARACTER SET koi8r FROM t1
231--echo ##################################################
232--cat_file $file
233--echo ##################################################
234TRUNCATE t2;
235--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
236--eval LOAD DATA INFILE '$file' INTO TABLE t2 CHARACTER SET koi8r
237--remove_file $file
238SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c;
239
240
241--echo # UTF-8 charset:
242
243--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
244--eval SELECT * INTO OUTFILE '$file' CHARACTER SET utf8 FROM t1
245--echo ##################################################
246--cat_file $file
247--echo ##################################################
248TRUNCATE t2;
249--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
250--eval LOAD DATA INFILE '$file' INTO TABLE t2 CHARACTER SET utf8
251--remove_file $file
252SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a, b, c;
253
254
255SET NAMES default;
256
257DROP TABLE t1, t2;
258###########################################################################
259
260--echo #
261--echo # Bug #53088: mysqldump with -T & --default-character-set set
262--echo #             truncates text/blob to 766 chars
263--echo #
264--echo # Also see mysqldump.test
265--echo #
266
267CREATE TABLE t1 (a BLOB) CHARSET latin1;
268CREATE TABLE t2 LIKE t1;
269
270let $file= '$MYSQLTEST_VARDIR/tmp/bug53088.txt';
271let $length= 800;
272
273--eval INSERT INTO t1 VALUES (REPEAT('.', $length))
274
275--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
276--eval SELECT * INTO OUTFILE $file CHARACTER SET latin1 FROM t1
277
278--echo # should be greater than $length
279--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
280--eval SELECT LENGTH(LOAD_FILE($file))
281
282--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
283--eval LOAD DATA INFILE $file INTO TABLE t2
284
285--remove_file $MYSQLTEST_VARDIR/tmp/bug53088.txt
286
287--echo # should be $length
288SELECT LENGTH(a) FROM t2;
289
290DROP TABLE t1, t2;
291
292
293###########################################################################
294--echo # End of 5.1 tests.
295