github.com/dolthub/go-mysql-server@v0.18.0/enginetest/queries/replace_queries.go (about)

     1  // Copyright 2020-2021 Dolthub, 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  // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    12  // See the License for the specific language governing permissions and
    13  // limitations under the License.
    14  
    15  package queries
    16  
    17  import (
    18  	"math"
    19  
    20  	"github.com/dolthub/go-mysql-server/sql"
    21  	"github.com/dolthub/go-mysql-server/sql/types"
    22  )
    23  
    24  // TODO: none of these tests insert into tables without primary key columns, which have different semantics for
    25  // REPLACE INTO queries. Add some tables / data without primary keys.
    26  var ReplaceQueries = []WriteQueryTest{
    27  	{
    28  		WriteQuery:          "REPLACE INTO mytable VALUES (1, 'first row');",
    29  		ExpectedWriteResult: []sql.Row{{types.NewOkResult(2)}},
    30  		SelectQuery:         "SELECT s FROM mytable WHERE i = 1;",
    31  		ExpectedSelect:      []sql.Row{{"first row"}},
    32  	},
    33  	{
    34  		WriteQuery:          "REPLACE INTO mytable SET i = 1, s = 'first row';",
    35  		ExpectedWriteResult: []sql.Row{{types.NewOkResult(2)}},
    36  		SelectQuery:         "SELECT s FROM mytable WHERE i = 1;",
    37  		ExpectedSelect:      []sql.Row{{"first row"}},
    38  	},
    39  	{
    40  		WriteQuery:          "REPLACE INTO mytable VALUES (1, 'new row same i');",
    41  		ExpectedWriteResult: []sql.Row{{types.NewOkResult(2)}},
    42  		SelectQuery:         "SELECT s FROM mytable WHERE i = 1;",
    43  		ExpectedSelect:      []sql.Row{{"new row same i"}},
    44  	},
    45  	{
    46  		WriteQuery:          "REPLACE INTO mytable SET i = 1, s = 'new row same i';",
    47  		ExpectedWriteResult: []sql.Row{{types.NewOkResult(2)}},
    48  		SelectQuery:         "SELECT s FROM mytable WHERE i = 1;",
    49  		ExpectedSelect:      []sql.Row{{"new row same i"}},
    50  	},
    51  	{
    52  		WriteQuery:          "REPLACE INTO mytable (s, i) VALUES ('x', 999);",
    53  		ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}},
    54  		SelectQuery:         "SELECT i FROM mytable WHERE s = 'x';",
    55  		ExpectedSelect:      []sql.Row{{int64(999)}},
    56  	},
    57  	{
    58  		WriteQuery:          "REPLACE INTO mytable SET s = 'x', i = 999;",
    59  		ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}},
    60  		SelectQuery:         "SELECT i FROM mytable WHERE s = 'x';",
    61  		ExpectedSelect:      []sql.Row{{int64(999)}},
    62  	},
    63  	{
    64  		WriteQuery:          "REPLACE INTO mytable VALUES (999, 'x');",
    65  		ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}},
    66  		SelectQuery:         "SELECT i FROM mytable WHERE s = 'x';",
    67  		ExpectedSelect:      []sql.Row{{int64(999)}},
    68  	},
    69  	{
    70  		WriteQuery:          "REPLACE INTO mytable SET i = 999, s = 'x';",
    71  		ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}},
    72  		SelectQuery:         "SELECT i FROM mytable WHERE s = 'x';",
    73  		ExpectedSelect:      []sql.Row{{int64(999)}},
    74  	},
    75  	{
    76  		WriteQuery: `REPLACE INTO typestable VALUES (
    77  			999, 127, 32767, 2147483647, 9223372036854775807,
    78  			255, 65535, 4294967295, 18446744073709551615,
    79  			3.40282346638528859811704183484516925440e+38, 1.797693134862315708145274237317043567981e+308,
    80  			'2037-04-05 12:51:36', '2231-11-07',
    81  			'random text', true, '{"key":"value"}', 'blobdata', 'v1', 'v2'
    82  			);`,
    83  		ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}},
    84  		SelectQuery:         "SELECT * FROM typestable WHERE id = 999;",
    85  		ExpectedSelect: []sql.Row{{
    86  			int64(999), int8(math.MaxInt8), int16(math.MaxInt16), int32(math.MaxInt32), int64(math.MaxInt64),
    87  			uint8(math.MaxUint8), uint16(math.MaxUint16), uint32(math.MaxUint32), uint64(math.MaxUint64),
    88  			float32(math.MaxFloat32), float64(math.MaxFloat64),
    89  			sql.MustConvert(types.Timestamp.Convert("2037-04-05 12:51:36")), sql.MustConvert(types.Date.Convert("2231-11-07")),
    90  			"random text", sql.True, types.MustJSON(`{"key":"value"}`), []byte("blobdata"), "v1", "v2",
    91  		}},
    92  	},
    93  	{
    94  		WriteQuery: `REPLACE INTO typestable SET
    95  			id = 999, i8 = 127, i16 = 32767, i32 = 2147483647, i64 = 9223372036854775807,
    96  			u8 = 255, u16 = 65535, u32 = 4294967295, u64 = 18446744073709551615,
    97  			f32 = 3.40282346638528859811704183484516925440e+38, f64 = 1.797693134862315708145274237317043567981e+308,
    98  			ti = '2037-04-05 12:51:36', da = '2231-11-07',
    99  			te = 'random text', bo = true, js = '{"key":"value"}', bl = 'blobdata', e1 = 'v1', s1 = 'v2'
   100  			;`,
   101  		ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}},
   102  		SelectQuery:         "SELECT * FROM typestable WHERE id = 999;",
   103  		ExpectedSelect: []sql.Row{{
   104  			int64(999), int8(math.MaxInt8), int16(math.MaxInt16), int32(math.MaxInt32), int64(math.MaxInt64),
   105  			uint8(math.MaxUint8), uint16(math.MaxUint16), uint32(math.MaxUint32), uint64(math.MaxUint64),
   106  			float32(math.MaxFloat32), float64(math.MaxFloat64),
   107  			sql.MustConvert(types.Timestamp.Convert("2037-04-05 12:51:36")), sql.MustConvert(types.Date.Convert("2231-11-07")),
   108  			"random text", sql.True, types.MustJSON(`{"key":"value"}`), []byte("blobdata"), "v1", "v2",
   109  		}},
   110  	},
   111  	{
   112  		SkipServerEngine: true, // the datetime returned is not non-zero
   113  		WriteQuery: `REPLACE INTO typestable VALUES (
   114  			999, -128, -32768, -2147483648, -9223372036854775808,
   115  			0, 0, 0, 0,
   116  			1.401298464324817070923729583289916131280e-45, 4.940656458412465441765687928682213723651e-324,
   117  			'0000-00-00 00:00:00', '0000-00-00',
   118  			'', false, '""', '', '', ''
   119  			);`,
   120  		ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}},
   121  		SelectQuery:         "SELECT * FROM typestable WHERE id = 999;",
   122  		ExpectedSelect: []sql.Row{{
   123  			int64(999), int8(-math.MaxInt8 - 1), int16(-math.MaxInt16 - 1), int32(-math.MaxInt32 - 1), int64(-math.MaxInt64 - 1),
   124  			uint8(0), uint16(0), uint32(0), uint64(0),
   125  			float32(math.SmallestNonzeroFloat32), float64(math.SmallestNonzeroFloat64),
   126  			types.Timestamp.Zero(), types.Date.Zero(),
   127  			"", sql.False, types.MustJSON(`""`), []byte(""), "", "",
   128  		}},
   129  	},
   130  	{
   131  		SkipServerEngine: true, // the datetime returned is not non-zero
   132  		WriteQuery: `REPLACE INTO typestable SET
   133  			id = 999, i8 = -128, i16 = -32768, i32 = -2147483648, i64 = -9223372036854775808,
   134  			u8 = 0, u16 = 0, u32 = 0, u64 = 0,
   135  			f32 = 1.401298464324817070923729583289916131280e-45, f64 = 4.940656458412465441765687928682213723651e-324,
   136  			ti = '0000-00-00 00:00:00', da = '0000-00-00',
   137  			te = '', bo = false, js = '""', bl = '', e1 = '', s1 = ''
   138  			;`,
   139  		ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}},
   140  		SelectQuery:         "SELECT * FROM typestable WHERE id = 999;",
   141  		ExpectedSelect: []sql.Row{{
   142  			int64(999), int8(-math.MaxInt8 - 1), int16(-math.MaxInt16 - 1), int32(-math.MaxInt32 - 1), int64(-math.MaxInt64 - 1),
   143  			uint8(0), uint16(0), uint32(0), uint64(0),
   144  			float32(math.SmallestNonzeroFloat32), float64(math.SmallestNonzeroFloat64),
   145  			types.Timestamp.Zero(), types.Date.Zero(),
   146  			"", sql.False, types.MustJSON(`""`), []byte(""), "", "",
   147  		}},
   148  	},
   149  	{
   150  		WriteQuery: `REPLACE INTO typestable VALUES (999, null, null, null, null, null, null, null, null,
   151  			null, null, null, null, null, null, null, null, null, null);`,
   152  		ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}},
   153  		SelectQuery:         "SELECT * FROM typestable WHERE id = 999;",
   154  		ExpectedSelect:      []sql.Row{{int64(999), nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil}},
   155  	},
   156  	{
   157  		WriteQuery: `REPLACE INTO typestable SET id=999, i8=null, i16=null, i32=null, i64=null, u8=null, u16=null, u32=null, u64=null,
   158  			f32=null, f64=null, ti=null, da=null, te=null, bo=null, js=null, bl=null, e1=null, s1=null;`,
   159  		ExpectedWriteResult: []sql.Row{{types.NewOkResult(1)}},
   160  		SelectQuery:         "SELECT * FROM typestable WHERE id = 999;",
   161  		ExpectedSelect:      []sql.Row{{int64(999), nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil}},
   162  	},
   163  }
   164  
   165  var ReplaceErrorTests = []GenericErrorQueryTest{
   166  	{
   167  		Name:  "too few values",
   168  		Query: "REPLACE INTO mytable (s, i) VALUES ('x');",
   169  	},
   170  	{
   171  		Name:  "too many values one column",
   172  		Query: "REPLACE INTO mytable (s) VALUES ('x', 999);",
   173  	},
   174  	{
   175  		Name:  "too many values two columns",
   176  		Query: "REPLACE INTO mytable (i, s) VALUES (999, 'x', 'y');",
   177  	},
   178  	{
   179  		Name:  "too few values no columns specified",
   180  		Query: "REPLACE INTO mytable VALUES (999);",
   181  	},
   182  	{
   183  		Name:  "too many values no columns specified",
   184  		Query: "REPLACE INTO mytable VALUES (999, 'x', 'y');",
   185  	},
   186  	{
   187  		Name:  "non-existent column values",
   188  		Query: "REPLACE INTO mytable (i, s, z) VALUES (999, 'x', 999);",
   189  	},
   190  	{
   191  		Name:  "non-existent column set",
   192  		Query: "REPLACE INTO mytable SET i = 999, s = 'x', z = 999;",
   193  	},
   194  	{
   195  		Name:  "duplicate column values",
   196  		Query: "REPLACE INTO mytable (i, s, s) VALUES (999, 'x', 'x');",
   197  	},
   198  	{
   199  		Name:  "duplicate column set",
   200  		Query: "REPLACE INTO mytable SET i = 999, s = 'y', s = 'y';",
   201  	},
   202  	{
   203  		Name:  "null given to non-nullable values",
   204  		Query: "INSERT INTO mytable (i, s) VALUES (null, 'y');",
   205  	},
   206  	{
   207  		Name:  "null given to non-nullable set",
   208  		Query: "INSERT INTO mytable SET i = null, s = 'y';",
   209  	},
   210  }