1
by brian
clean slate |
1 |
DROP TABLE IF EXISTS t1, t2; |
2 |
#
|
|
3 |
# Bug#31663 FIELDS TERMINATED BY special character |
|
4 |
#
|
|
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; |
|
9 |
Warnings: |
|
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= |
|
14 |
||
15 |
CREATE TABLE t2 SELECT * FROM t1; |
|
16 |
LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 FIELDS TERMINATED BY 'raker'; |
|
17 |
Warnings: |
|
18 |
Warning 1262 Row 1 was truncated; it contained more data than there were input columns |
|
19 |
SELECT * FROM t2; |
|
20 |
i1 i2 c1 c2 |
|
21 |
101 202 -r- =raker= |
|
22 |
101 202 -r- = |
|
23 |
DROP TABLE t2; |
|
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') |
|
28 |
101r202 |
|
29 |
||
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; |
|
33 |
i1 i2 |
|
34 |
101 202 |
|
35 |
101 202 |
|
36 |
DROP TABLE t2; |
|
37 |
# FIELDS TERMINATED BY '0', warning: |
|
38 |
SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' FIELDS TERMINATED BY '0' FROM t1; |
|
39 |
Warnings: |
|
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') |
|
43 |
10102020-r-0=raker= |
|
44 |
||
45 |
CREATE TABLE t2 SELECT * FROM t1; |
|
46 |
LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug31663.txt' INTO TABLE t2 FIELDS TERMINATED BY '0'; |
|
47 |
Warnings: |
|
48 |
Warning 1262 Row 1 was truncated; it contained more data than there were input columns |
|
49 |
SELECT * FROM t2; |
|
50 |
i1 i2 c1 c2 |
|
51 |
101 202 -r- =raker= |
|
52 |
1 1 2 2 |
|
53 |
DROP TABLE t2; |
|
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; |
|
56 |
Warnings: |
|
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=" |
|
61 |
||
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'; |
|
64 |
Warnings: |
|
65 |
Warning 1262 Row 1 was truncated; it contained more data than there were input columns |
|
66 |
SELECT * FROM t2; |
|
67 |
i1 i2 c1 c2 |
|
68 |
101 202 -r- =raker= |
|
69 |
1 1 2 2 |
|
70 |
DROP TABLE t2; |
|
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') |
|
75 |
"-r-"0"=raker=" |
|
76 |
||
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; |
|
80 |
c1 c2 |
|
81 |
-r- =raker= |
|
82 |
-r- =raker= |
|
83 |
DROP TABLE t2; |
|
84 |
DROP TABLE t1; |
|
85 |
#
|
|
86 |
# Bug#32533: SELECT INTO OUTFILE never escapes multibyte character |
|
87 |
#
|
|
88 |
CREATE TABLE t1 (c1 VARCHAR(256)); |
|
89 |
INSERT INTO t1 VALUES (0xC3); |
|
90 |
SELECT HEX(c1) FROM t1; |
|
91 |
HEX(c1) |
|
92 |
C3
|
|
93 |
SELECT * INTO OUTFILE 'MYSQLTEST_VARDIR/tmp/bug32533.txt' FIELDS ENCLOSED BY 0xC3 FROM t1; |
|
94 |
TRUNCATE t1; |
|
95 |
SELECT HEX(LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug32533.txt')); |
|
96 |
HEX(LOAD_FILE('MYSQLTEST_VARDIR/tmp/bug32533.txt')) |
|
97 |
C35CC3C30A
|
|
98 |
LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug32533.txt' INTO TABLE t1 FIELDS ENCLOSED BY 0xC3; |
|
99 |
SELECT HEX(c1) FROM t1; |
|
100 |
HEX(c1) |
|
101 |
C3
|
|
102 |
DROP TABLE t1; |
|
103 |
# End of 5.0 tests. |