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  }