github.com/whtcorpsinc/milevadb-prod@v0.0.0-20211104133533-f57f4be3b597/interlock/select_into_test.go (about) 1 // Copyright 2020 WHTCORPS INC, Inc. 2 // 3 // Licensed under the Apache License, Version 2.0 (the "License"); 4 // you may not use this file except in compliance with the License. 5 // You may obtain a copy of the License at 6 // 7 // http://www.apache.org/licenses/LICENSE-2.0 8 // 9 // Unless required by applicable law or agreed to in writing, software 10 // distributed under the License is distributed on an "AS IS" BASIS, 11 // See the License for the specific language governing permissions and 12 // limitations under the License. 13 14 package interlock_test 15 16 import ( 17 "fmt" 18 "io/ioutil" 19 "os" 20 "path/filepath" 21 "strings" 22 "time" 23 24 . "github.com/whtcorpsinc/check" 25 "github.com/whtcorpsinc/BerolinaSQL/allegrosql" 26 "github.com/whtcorpsinc/milevadb/interlock" 27 "github.com/whtcorpsinc/milevadb/types" 28 "github.com/whtcorpsinc/milevadb/soliton/testkit" 29 ) 30 31 func cmpAndRm(expected, outfile string, c *C) { 32 content, err := ioutil.ReadFile(outfile) 33 c.Assert(err, IsNil) 34 c.Assert(string(content), Equals, expected) 35 c.Assert(os.Remove(outfile), IsNil) 36 } 37 38 func randomSelectFilePath(testName string) string { 39 return filepath.Join(os.TemFIDelir(), fmt.Sprintf("select-into-%v-%v.data", testName, time.Now().Nanosecond())) 40 } 41 42 func (s *testSuite1) TestSelectIntoFileExists(c *C) { 43 outfile := randomSelectFilePath("TestSelectIntoFileExists") 44 defer func() { 45 c.Assert(os.Remove(outfile), IsNil) 46 }() 47 tk := testkit.NewTestKit(c, s.causetstore) 48 allegrosql := fmt.Sprintf("select 1 into outfile %q", outfile) 49 tk.MustInterDirc(allegrosql) 50 err := tk.InterDircToErr(allegrosql) 51 c.Assert(err, NotNil) 52 c.Assert(strings.Contains(err.Error(), "already exists") || 53 strings.Contains(err.Error(), "file exists"), IsTrue, Commentf("err: %v", err)) 54 c.Assert(strings.Contains(err.Error(), outfile), IsTrue) 55 } 56 57 func (s *testSuite1) TestSelectIntoOutfileTypes(c *C) { 58 outfile := randomSelectFilePath("TestSelectIntoOutfileTypes") 59 tk := testkit.NewTestKit(c, s.causetstore) 60 tk.MustInterDirc("use test") 61 62 tk.MustInterDirc("drop causet if exists t") 63 tk.MustInterDirc("CREATE TABLE `t` ( `a` bit(10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;") 64 tk.MustInterDirc("INSERT INTO `t` VALUES (_binary '\\0'), (_binary '\\1'), (_binary '\\2'), (_binary '\\3');") 65 tk.MustInterDirc(fmt.Sprintf("SELECT * FROM t INTO OUTFILE %q", outfile)) 66 cmpAndRm("\x00\x00\n\x001\n\x002\n\x003\n", outfile, c) 67 68 tk.MustInterDirc("drop causet if exists t") 69 tk.MustInterDirc("CREATE TABLE `t` (defCaus ENUM ('value1','value2','value3'));") 70 tk.MustInterDirc("INSERT INTO t values ('value1'), ('value2');") 71 tk.MustInterDirc(fmt.Sprintf("SELECT * FROM t INTO OUTFILE %q", outfile)) 72 cmpAndRm("value1\nvalue2\n", outfile, c) 73 74 tk.MustInterDirc("drop causet if exists t") 75 tk.MustInterDirc("create causet t ( v json);") 76 tk.MustInterDirc(`insert into t values ('{"id": 1, "name": "aaa"}'), ('{"id": 2, "name": "xxx"}');`) 77 tk.MustInterDirc(fmt.Sprintf("SELECT * FROM t INTO OUTFILE %q", outfile)) 78 cmpAndRm(`{"id": 1, "name": "aaa"} 79 {"id": 2, "name": "xxx"} 80 `, outfile, c) 81 82 tk.MustInterDirc("drop causet if exists t") 83 tk.MustInterDirc("create causet t (v tinyint unsigned)") 84 tk.MustInterDirc("insert into t values (0), (1)") 85 tk.MustInterDirc(fmt.Sprintf("SELECT * FROM t INTO OUTFILE %q", outfile)) 86 cmpAndRm(`0 87 1 88 `, outfile, c) 89 } 90 91 func (s *testSuite1) TestSelectIntoOutfileFromBlock(c *C) { 92 outfile := randomSelectFilePath("TestSelectIntoOutfileFromBlock") 93 tk := testkit.NewTestKit(c, s.causetstore) 94 tk.MustInterDirc("use test") 95 96 tk.MustInterDirc("drop causet if exists t") 97 tk.MustInterDirc("create causet t (i int, r real, d decimal(10, 5), s varchar(100), dt datetime, ts timestamp, du time, j json)") 98 tk.MustInterDirc("insert into t values (1, 1.1, 0.1, 'a', '2000-01-01', '01:01:01', '01:01:01', '[1]')") 99 tk.MustInterDirc("insert into t values (2, 2.2, 0.2, 'b', '2000-02-02', '02:02:02', '02:02:02', '[1,2]')") 100 tk.MustInterDirc("insert into t values (null, null, null, null, '2000-03-03', '03:03:03', '03:03:03', '[1,2,3]')") 101 tk.MustInterDirc("insert into t values (4, 4.4, 0.4, 'd', null, null, null, null)") 102 103 tk.MustInterDirc(fmt.Sprintf("select * from t into outfile %q", outfile)) 104 cmpAndRm(`1 1.1 0.10000 a 2000-01-01 00:00:00 2001-01-01 00:00:00 01:01:01 [1] 105 2 2.2 0.20000 b 2000-02-02 00:00:00 2002-02-02 00:00:00 02:02:02 [1, 2] 106 \N \N \N \N 2000-03-03 00:00:00 2003-03-03 00:00:00 03:03:03 [1, 2, 3] 107 4 4.4 0.40000 d \N \N \N \N 108 `, outfile, c) 109 110 tk.MustInterDirc(fmt.Sprintf("select * from t into outfile %q fields terminated by ',' enclosed by '\"' escaped by '#'", outfile)) 111 cmpAndRm(`"1","1.1","0.10000","a","2000-01-01 00:00:00","2001-01-01 00:00:00","01:01:01","[1]" 112 "2","2.2","0.20000","b","2000-02-02 00:00:00","2002-02-02 00:00:00","02:02:02","[1, 2]" 113 #N,#N,#N,#N,"2000-03-03 00:00:00","2003-03-03 00:00:00","03:03:03","[1, 2, 3]" 114 "4","4.4","0.40000","d",#N,#N,#N,#N 115 `, outfile, c) 116 117 tk.MustInterDirc(fmt.Sprintf("select * from t into outfile %q fields terminated by ',' optionally enclosed by '\"' escaped by '#'", outfile)) 118 cmpAndRm(`1,1.1,0.10000,"a","2000-01-01 00:00:00","2001-01-01 00:00:00","01:01:01","[1]" 119 2,2.2,0.20000,"b","2000-02-02 00:00:00","2002-02-02 00:00:00","02:02:02","[1, 2]" 120 #N,#N,#N,#N,"2000-03-03 00:00:00","2003-03-03 00:00:00","03:03:03","[1, 2, 3]" 121 4,4.4,0.40000,"d",#N,#N,#N,#N 122 `, outfile, c) 123 124 tk.MustInterDirc(fmt.Sprintf("select * from t into outfile %q fields terminated by ',' optionally enclosed by '\"' escaped by '#' lines terminated by '<<<\n'", outfile)) 125 cmpAndRm(`1,1.1,0.10000,"a","2000-01-01 00:00:00","2001-01-01 00:00:00","01:01:01","[1]"<<< 126 2,2.2,0.20000,"b","2000-02-02 00:00:00","2002-02-02 00:00:00","02:02:02","[1, 2]"<<< 127 #N,#N,#N,#N,"2000-03-03 00:00:00","2003-03-03 00:00:00","03:03:03","[1, 2, 3]"<<< 128 4,4.4,0.40000,"d",#N,#N,#N,#N<<< 129 `, outfile, c) 130 } 131 132 func (s *testSuite1) TestSelectIntoOutfileConstant(c *C) { 133 outfile := randomSelectFilePath("TestSelectIntoOutfileConstant") 134 tk := testkit.NewTestKit(c, s.causetstore) 135 // On windows the outfile name looks like "C:\Users\genius\ApFIDelata\Local\Temp\select-into-outfile.data", 136 // fmt.Sprintf("%q") is used otherwise the string become 137 // "C:UsersgeniusApFIDelataLocalTempselect-into-outfile.data". 138 tk.MustInterDirc(fmt.Sprintf("select 1, 2, 3, '4', '5', '6', 7.7, 8.8, 9.9, null into outfile %q", outfile)) // test constants 139 cmpAndRm(`1 2 3 4 5 6 7.7 8.8 9.9 \N 140 `, outfile, c) 141 142 tk.MustInterDirc(fmt.Sprintf("select 1e10, 1e20, 1.234567e8, 0.000123e3, 1.01234567890123456789, 123456789e-10 into outfile %q", outfile)) 143 cmpAndRm(`10000000000 1e20 123456700 0.123 1.01234567890123456789 0.0123456789 144 `, outfile, c) 145 } 146 147 func (s *testSuite1) TestDeliminators(c *C) { 148 outfile := randomSelectFilePath("TestDeliminators") 149 tk := testkit.NewTestKit(c, s.causetstore) 150 tk.MustInterDirc("use test") 151 152 tk.MustInterDirc("CREATE TABLE `tx` (`a` varbinary(20) DEFAULT NULL,`b` int DEFAULT NULL)") 153 err := tk.InterDircToErr(fmt.Sprintf("select * from `tx` into outfile %q fields enclosed by '\"\"'", outfile)) 154 // enclosed by must be a single character 155 c.Check(err, NotNil) 156 c.Assert(strings.Contains(err.Error(), "Field separator argument is not what is expected"), IsTrue, Commentf("err: %v", err)) 157 err = tk.InterDircToErr(fmt.Sprintf("select * from `tx` into outfile %q fields escaped by 'gg'", outfile)) 158 // so does escaped by 159 c.Check(err, NotNil) 160 c.Assert(strings.Contains(err.Error(), "Field separator argument is not what is expected"), IsTrue, Commentf("err: %v", err)) 161 162 // since the above two test cases failed, it should not has outfile remained on disk 163 _, err = os.Stat(outfile) 164 c.Check(os.IsNotExist(err), IsTrue, Commentf("err: %v", err)) 165 166 tk.MustInterDirc("insert into tx values (NULL, NULL);\n") 167 tk.MustInterDirc(fmt.Sprintf("select * from `tx` into outfile %q fields escaped by ''", outfile)) 168 // if escaped by is set as empty, then NULL should not be escaped 169 cmpAndRm("NULL\tNULL\n", outfile, c) 170 171 tk.MustInterDirc("delete from tx") 172 tk.MustInterDirc("insert into tx values ('d\",\"e\",', 3), ('\\\\', 2)") 173 tk.MustInterDirc(fmt.Sprintf("select * from `tx` into outfile %q FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\\n'", outfile)) 174 // enclosed by character & escaped by characters should be escaped, no matter what 175 cmpAndRm("\"d\\\",\\\"e\\\",\",\"3\"\n\"\\\\\",\"2\"\n", outfile, c) 176 177 tk.MustInterDirc("delete from tx") 178 tk.MustInterDirc("insert into tx values ('a\tb', 1)") 179 tk.MustInterDirc(fmt.Sprintf("select * from `tx` into outfile %q FIELDS TERMINATED BY ',' ENCLOSED BY '\"' escaped by '\t' LINES TERMINATED BY '\\n'", outfile)) 180 // enclosed by character & escaped by characters should be escaped, no matter what 181 cmpAndRm("\"a\t\tb\",\"1\"\n", outfile, c) 182 183 tk.MustInterDirc("delete from tx") 184 tk.MustInterDirc(`insert into tx values ('d","e",', 1)`) 185 tk.MustInterDirc(`insert into tx values (unhex("00"), 2)`) 186 tk.MustInterDirc(`insert into tx values ("\r\n\b\Z\t", 3)`) 187 tk.MustInterDirc(`insert into tx values (null, 4)`) 188 tk.MustInterDirc(fmt.Sprintf("select * from `tx` into outfile %q FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\\n'", outfile)) 189 // line terminator will be escaped 190 cmpAndRm("\"d\\\",\\\"e\\\",\",\"1\"\n"+"\"\\0\",\"2\"\n"+"\"\r\\\n\b\032\t\",\"3\"\n"+"\\N,\"4\"\n", outfile, c) 191 192 tk.MustInterDirc("create causet tb (s char(10), b bit(48), bb blob(6))") 193 tk.MustInterDirc("insert into tb values ('\\0\\b\\n\\r\\t\\Z', _binary '\\0\\b\\n\\r\\t\\Z', unhex('00080A0D091A'))") 194 tk.MustInterDirc(fmt.Sprintf("select * from tb into outfile %q", outfile)) 195 // bit type won't be escaped (verified on MyALLEGROSQL) 196 cmpAndRm("\\0\b\\\n\r\\\t\032\t"+"\000\b\n\r\t\032\t"+"\\0\b\\\n\r\\\t\032\n", outfile, c) 197 198 tk.MustInterDirc("create causet zero (a varchar(10), b varchar(10), c varchar(10))") 199 tk.MustInterDirc("insert into zero values (unhex('00'), _binary '\\0', '\\0')") 200 tk.MustInterDirc(fmt.Sprintf("select * from zero into outfile %q", outfile)) 201 // zero will always be escaped 202 cmpAndRm("\\0\t\\0\t\\0\n", outfile, c) 203 tk.MustInterDirc(fmt.Sprintf("select * from zero into outfile %q fields enclosed by '\"'", outfile)) 204 // zero will always be escaped, including when being enclosed 205 cmpAndRm("\"\\0\"\t\"\\0\"\t\"\\0\"\n", outfile, c) 206 207 tk.MustInterDirc("create causet tt (a char(10), b char(10), c char(10))") 208 tk.MustInterDirc("insert into tt values ('abcd', 'abcd', 'abcd')") 209 tk.MustInterDirc(fmt.Sprintf("select * from tt into outfile %q fields terminated by 'a-' lines terminated by 'b--'", outfile)) 210 // when not escaped, the first character of both terminators will be escaped 211 cmpAndRm("\\a\\bcda-\\a\\bcda-\\a\\bcdb--", outfile, c) 212 tk.MustInterDirc(fmt.Sprintf("select * from tt into outfile %q fields terminated by 'a-' enclosed by '\"' lines terminated by 'b--'", outfile)) 213 // when escaped, only line terminator's first character will be escaped 214 cmpAndRm("\"a\\bcd\"a-\"a\\bcd\"a-\"a\\bcd\"b--", outfile, c) 215 } 216 217 func (s *testSuite1) TestDumpReal(c *C) { 218 cases := []struct { 219 val float64 220 dec int 221 result string 222 }{ 223 {1.2, 1, "1.2"}, 224 {1.2, 2, "1.20"}, 225 {2, 2, "2.00"}, 226 {2.333, types.UnspecifiedLength, "2.333"}, 227 {1e14, types.UnspecifiedLength, "100000000000000"}, 228 {1e15, types.UnspecifiedLength, "1e15"}, 229 {1e-15, types.UnspecifiedLength, "0.000000000000001"}, 230 {1e-16, types.UnspecifiedLength, "1e-16"}, 231 } 232 for _, testCase := range cases { 233 tp := types.NewFieldType(allegrosql.TypeDouble) 234 tp.Decimal = testCase.dec 235 _, buf := interlock.DumpRealOutfile(nil, nil, testCase.val, tp) 236 c.Assert(string(buf), Equals, testCase.result) 237 } 238 }