github.com/dolthub/go-mysql-server@v0.18.0/sql/expression/function/date_test.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 function
    16  
    17  import (
    18  	"testing"
    19  	"time"
    20  
    21  	"github.com/stretchr/testify/require"
    22  
    23  	"github.com/dolthub/go-mysql-server/sql"
    24  	"github.com/dolthub/go-mysql-server/sql/expression"
    25  	"github.com/dolthub/go-mysql-server/sql/types"
    26  )
    27  
    28  func TestDateAdd(t *testing.T) {
    29  	require := require.New(t)
    30  	ctx := sql.NewEmptyContext()
    31  
    32  	_, err := NewDateAdd()
    33  	require.Error(err)
    34  
    35  	_, err = NewDateAdd(expression.NewLiteral("2018-05-02", types.LongText))
    36  	require.Error(err)
    37  
    38  	_, err = NewDateAdd(expression.NewLiteral("2018-05-02", types.LongText),
    39  		expression.NewLiteral(int64(1), types.Int64),
    40  	)
    41  	require.Error(err)
    42  
    43  	f, err := NewDateAdd(expression.NewGetField(0, types.Text, "foo", false),
    44  		expression.NewInterval(
    45  			expression.NewLiteral(int64(1), types.Int64),
    46  			"DAY",
    47  		),
    48  	)
    49  	require.NoError(err)
    50  
    51  	expected := time.Date(2018, time.May, 3, 0, 0, 0, 0, time.UTC)
    52  
    53  	result, err := f.Eval(ctx, sql.Row{"2018-05-02"})
    54  	require.NoError(err)
    55  	require.Equal(expected, result)
    56  
    57  	result, err = f.Eval(ctx, sql.Row{"12:34:56"})
    58  	require.NoError(err)
    59  	require.Nil(result)
    60  
    61  	result, err = f.Eval(ctx, sql.Row{nil})
    62  	require.NoError(err)
    63  	require.Nil(result)
    64  
    65  	result, err = f.Eval(ctx, sql.Row{"asdasdasd"})
    66  	require.NoError(err)
    67  	require.Nil(result)
    68  }
    69  
    70  // MySQL's SUBDATE function is just syntactic sugar on top of DATE_SUB. The first param is the date, and the
    71  // second is the value to subtract. If the second param is an interval type, it gets passed to DATE_SUB as-is. If
    72  // it is not an explicit interval, the interval period is assumed to be "DAY".
    73  func TestSubDate(t *testing.T) {
    74  	require := require.New(t)
    75  	ctx := sql.NewEmptyContext()
    76  
    77  	// Not enough params
    78  	_, err := NewSubDate()
    79  	require.Error(err)
    80  
    81  	// Not enough params
    82  	_, err = NewSubDate(expression.NewLiteral("2018-05-02", types.LongText))
    83  	require.Error(err)
    84  
    85  	// If the second argument is NOT an interval, then it's assumed to be a day interval
    86  	f, err := NewSubDate(
    87  		expression.NewLiteral("2018-05-02", types.LongText),
    88  		expression.NewLiteral(int64(1), types.Int64))
    89  	require.NoError(err)
    90  	expected := time.Date(2018, time.May, 1, 0, 0, 0, 0, time.UTC)
    91  	result, err := f.Eval(ctx, sql.Row{})
    92  	require.NoError(err)
    93  	require.Equal(expected, result)
    94  
    95  	// If the second argument is an interval, then SUBDATE works exactly like DATE_SUB
    96  	f, err = NewSubDate(
    97  		expression.NewGetField(0, types.Text, "foo", false),
    98  		expression.NewInterval(expression.NewLiteral(int64(1), types.Int64), "DAY"))
    99  	require.NoError(err)
   100  	result, err = f.Eval(ctx, sql.Row{"2018-05-02"})
   101  	require.NoError(err)
   102  	require.Equal(expected, result)
   103  
   104  	// If the interval param is NULL, then NULL is returned
   105  	f2, err := NewSubDate(
   106  		expression.NewLiteral("2018-05-02", types.LongText),
   107  		expression.NewGetField(0, types.Int64, "foo", true))
   108  	result, err = f2.Eval(ctx, sql.Row{nil})
   109  	require.NoError(err)
   110  	require.Nil(result)
   111  
   112  	// If the date param is NULL, then NULL is returned
   113  	result, err = f.Eval(ctx, sql.Row{nil})
   114  	require.NoError(err)
   115  	require.Nil(result)
   116  
   117  	// If a time is passed (and no date) then NULL is returned
   118  	result, err = f.Eval(ctx, sql.Row{"12:00:56"})
   119  	require.NoError(err)
   120  	require.Nil(result)
   121  
   122  	// If an invalid date is passed, then NULL is returned
   123  	result, err = f.Eval(ctx, sql.Row{"asdasdasd"})
   124  	require.NoError(err)
   125  	require.Nil(result)
   126  }
   127  
   128  func TestDateSub(t *testing.T) {
   129  	require := require.New(t)
   130  	ctx := sql.NewEmptyContext()
   131  
   132  	_, err := NewDateSub()
   133  	require.Error(err)
   134  
   135  	_, err = NewDateSub(expression.NewLiteral("2018-05-02", types.LongText))
   136  	require.Error(err)
   137  
   138  	_, err = NewDateSub(expression.NewLiteral("2018-05-02", types.LongText),
   139  		expression.NewLiteral(int64(1), types.Int64),
   140  	)
   141  	require.Error(err)
   142  
   143  	f, err := NewDateSub(expression.NewGetField(0, types.Text, "foo", false),
   144  		expression.NewInterval(
   145  			expression.NewLiteral(int64(1), types.Int64),
   146  			"DAY",
   147  		),
   148  	)
   149  	require.NoError(err)
   150  
   151  	expected := time.Date(2018, time.May, 1, 0, 0, 0, 0, time.UTC)
   152  
   153  	result, err := f.Eval(ctx, sql.Row{"2018-05-02"})
   154  	require.NoError(err)
   155  	require.Equal(expected, result)
   156  
   157  	result, err = f.Eval(ctx, sql.Row{"12:34:56"})
   158  	require.NoError(err)
   159  	require.Nil(result)
   160  
   161  	result, err = f.Eval(ctx, sql.Row{nil})
   162  	require.NoError(err)
   163  	require.Nil(result)
   164  
   165  	result, err = f.Eval(ctx, sql.Row{"asdasdasd"})
   166  	require.NoError(err)
   167  	require.Nil(result)
   168  }
   169  
   170  func TestUnixTimestamp(t *testing.T) {
   171  	require := require.New(t)
   172  
   173  	ctx := sql.NewEmptyContext()
   174  	_, err := NewUnixTimestamp()
   175  	require.NoError(err)
   176  
   177  	_, err = NewUnixTimestamp(expression.NewLiteral("2018-05-02", types.LongText))
   178  	require.NoError(err)
   179  
   180  	_, err = NewUnixTimestamp(expression.NewLiteral("2018-05-02", types.LongText))
   181  	require.NoError(err)
   182  
   183  	_, err = NewUnixTimestamp(expression.NewLiteral("2018-05-02", types.LongText), expression.NewLiteral("2018-05-02", types.LongText))
   184  	require.Error(err)
   185  
   186  	date := time.Date(2018, time.December, 2, 16, 25, 0, 0, time.Local)
   187  	testNowFunc := func() time.Time {
   188  		return date
   189  	}
   190  
   191  	var ctx2 *sql.Context
   192  	err = sql.RunWithNowFunc(testNowFunc, func() error {
   193  		ctx2 = sql.NewEmptyContext()
   194  		return nil
   195  	})
   196  	require.NoError(err)
   197  
   198  	var ut sql.Expression
   199  	var expected interface{}
   200  	ut = &UnixTimestamp{nil}
   201  	expected = float64(date.Unix())
   202  	result, err := ut.Eval(ctx2, nil)
   203  	require.NoError(err)
   204  	require.Equal(expected, result)
   205  	require.Equal(uint16(0), ctx.WarningCount())
   206  
   207  	ut, err = NewUnixTimestamp(expression.NewLiteral("2018-05-02", types.LongText))
   208  	require.NoError(err)
   209  	expected = float64(time.Date(2018, 5, 2, 0, 0, 0, 0, time.UTC).Unix())
   210  	result, err = ut.Eval(ctx, nil)
   211  	require.NoError(err)
   212  	require.Equal(expected, result)
   213  	require.Equal(uint16(0), ctx.WarningCount())
   214  
   215  	ut, err = NewUnixTimestamp(expression.NewLiteral(nil, types.Null))
   216  	require.NoError(err)
   217  	expected = nil
   218  	result, err = ut.Eval(ctx, nil)
   219  	require.NoError(err)
   220  	require.Equal(expected, result)
   221  	require.Equal(uint16(0), ctx.WarningCount())
   222  
   223  	// When MySQL can't convert the expression to a date, it always returns 0 and sets a warning
   224  	ut, err = NewUnixTimestamp(expression.NewLiteral(1577995200, types.Int64))
   225  	require.NoError(err)
   226  	result, err = ut.Eval(ctx, nil)
   227  	require.NoError(err)
   228  	require.Equal(0, result)
   229  	require.Equal(uint16(1), ctx.WarningCount())
   230  	require.Equal("Incorrect datetime value: 1577995200", ctx.Warnings()[0].Message)
   231  	require.Equal(1292, ctx.Warnings()[0].Code)
   232  
   233  	// When MySQL can't convert the expression to a date, it always returns 0 and sets a warning
   234  	ctx.ClearWarnings()
   235  	// TODO: ClearWarnings has to be called twice to actually clear the warnings because of the way it sets its
   236  	//       warncnt member var. This should be fixed, but existing behavior depends on this behavior currently.
   237  	ctx.ClearWarnings()
   238  	ut, err = NewUnixTimestamp(expression.NewLiteral("d0lthub", types.Text))
   239  	require.NoError(err)
   240  	result, err = ut.Eval(ctx, nil)
   241  	require.NoError(err)
   242  	require.Equal(0, result)
   243  	require.Equal(uint16(1), ctx.WarningCount())
   244  	require.Equal("Incorrect datetime value: 'd0lthub'", ctx.Warnings()[0].Message)
   245  	require.Equal(1292, ctx.Warnings()[0].Code)
   246  }
   247  
   248  func TestFromUnixtime(t *testing.T) {
   249  	require := require.New(t)
   250  
   251  	_, err := NewUnixTimestamp(expression.NewLiteral(0, types.Int64))
   252  	require.NoError(err)
   253  
   254  	_, err = NewUnixTimestamp(expression.NewLiteral(1447430881, types.Int64))
   255  	require.NoError(err)
   256  }