github.com/dolthub/go-mysql-server@v0.18.0/sql/expression/like_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 expression
    16  
    17  import (
    18  	"fmt"
    19  	"testing"
    20  
    21  	"github.com/stretchr/testify/require"
    22  
    23  	"github.com/dolthub/go-mysql-server/sql"
    24  	"github.com/dolthub/go-mysql-server/sql/types"
    25  )
    26  
    27  func TestPatternToRegex(t *testing.T) {
    28  	testCases := []struct {
    29  		in, out string
    30  	}{
    31  		{`__`, `(?s)^..$`},
    32  		{`_%_`, `(?s)^..*.$`},
    33  		{`%_`, `(?s)^.*.$`},
    34  		{`_%`, `(?s)^..*$`},
    35  		{`a_b`, `(?s)^a.b$`},
    36  		{`a%b`, `(?s)^a.*b$`},
    37  		{`a.%b`, `(?s)^a\..*b$`},
    38  		{`a\%b`, `(?s)^a%b$`},
    39  		{`a\_b`, `(?s)^a_b$`},
    40  		{`a\\b`, `(?s)^a\\b$`},
    41  		{`a\\\_b`, `(?s)^a\\_b$`},
    42  		{`(ab)`, `(?s)^\(ab\)$`},
    43  		{`$`, `(?s)^\$$`},
    44  		{`$$`, `(?s)^\$\$$`},
    45  	}
    46  
    47  	for _, tt := range testCases {
    48  		t.Run(tt.in, func(t *testing.T) {
    49  			require.Equal(t, tt.out, patternToGoRegex(tt.in))
    50  		})
    51  	}
    52  }
    53  
    54  func TestCustomPatternToRegex(t *testing.T) {
    55  	testCases := []struct {
    56  		in, out, escape string
    57  	}{
    58  		{`a%`, `(?s)^%$`, `a`},
    59  		{`a_`, `(?s)^_$`, `a`},
    60  		{`\_`, `(?s)^_$`, `a`},
    61  		{`\_`, `(?s)^_$`, `\`},
    62  		{`a%a%`, `(?s)^%%$`, `a`},
    63  		{`a%a_`, `(?s)^%_$`, `a`},
    64  		{`$%`, `(?s)^%$`, `$`},
    65  		{`$%$%`, `(?s)^%%$`, `$`},
    66  		{`$$`, `(?s)^\$$`, `$`},
    67  		{`$\`, `(?s)^\\$`, `$`},
    68  		{`\$`, `(?s)^\$$`, `$`},
    69  	}
    70  
    71  	for _, tt := range testCases {
    72  		t.Run(tt.in, func(t *testing.T) {
    73  			require.Equal(t, tt.out, patternToGoRegexWithEscape(tt.in, tt.escape))
    74  		})
    75  	}
    76  }
    77  
    78  func TestLike(t *testing.T) {
    79  	testCases := []struct {
    80  		pattern, value, escape string
    81  		ok                     bool
    82  		collation              sql.CollationID
    83  	}{
    84  		{"a__", "abc", "", true, sql.Collation_Default},
    85  		{"a__", "abcd", "", false, sql.Collation_Default},
    86  		{"a%b", "acb", "", true, sql.Collation_Default},
    87  		{"a%b", "acdkeflskjfdklb", "", true, sql.Collation_Default},
    88  		{"a%b", "ab", "", true, sql.Collation_Default},
    89  		{"a%b", "a", "", false, sql.Collation_Default},
    90  		{"a_b", "ab", "", false, sql.Collation_Default},
    91  		{"aa:%", "aa:bb:cc:dd:ee:ff", "", true, sql.Collation_Default},
    92  		{"aa:%", "AA:BB:CC:DD:EE:FF", "", false, sql.Collation_Default},
    93  		{"aa:%", "AA:BB:CC:DD:EE:FF", "", true, sql.Collation_utf8mb4_0900_ai_ci},
    94  		{"a_%_b%_%c", "AaAbCc", "", true, sql.Collation_utf8mb4_0900_ai_ci},
    95  		{"a_%_b%_%c", "AaAbBcCbCc", "", true, sql.Collation_utf8mb4_0900_ai_ci},
    96  		{"a_%_b%_%c", "AbbbbC", "", true, sql.Collation_utf8mb4_0900_ai_ci},
    97  		{"a_%_n%_%z", "aBcDeFgHiJkLmNoPqRsTuVwXyZ", "", true, sql.Collation_utf8mb4_0900_ai_ci},
    98  		{`a\%b`, "acb", "", false, sql.Collation_Default},
    99  		{`a\%b`, "a%b", "", true, sql.Collation_Default},
   100  		{`a\%b`, "A%B", "", false, sql.Collation_Default},
   101  		{`a\%b`, "A%B", "", true, sql.Collation_utf8mb4_0900_ai_ci},
   102  		{"a$%b", "acb", "$", false, sql.Collation_Default},
   103  		{"a$%b", "a%b", "$", true, sql.Collation_Default},
   104  		{"a$%b", "A%B", "$", false, sql.Collation_Default},
   105  		{"a$%b", "A%B", "$", true, sql.Collation_utf8mb4_0900_ai_ci},
   106  		{`a`, "a", "", true, sql.Collation_Default},
   107  		{`ab`, "a", "", false, sql.Collation_Default},
   108  		{`a\b`, "a", "", false, sql.Collation_Default},
   109  		{`a\\b`, "a", "", false, sql.Collation_Default},
   110  		{`a\\\b`, "a", "", false, sql.Collation_Default},
   111  		{`a`, "a", "", true, sql.Collation_utf8mb4_0900_ai_ci},
   112  		{`ab`, "a", "", false, sql.Collation_utf8mb4_0900_ai_ci},
   113  		{`a\b`, "a", "", false, sql.Collation_utf8mb4_0900_ai_ci},
   114  		{`a\\b`, "a", "", false, sql.Collation_utf8mb4_0900_ai_ci},
   115  		{`a\\\b`, "a", "", false, sql.Collation_utf8mb4_0900_ai_ci},
   116  		{`A%%%%`, "abc", "", true, sql.Collation_utf8mb4_0900_ai_ci},
   117  		{`A%%%%bc`, "abc", "", true, sql.Collation_utf8mb4_0900_ai_ci},
   118  	}
   119  
   120  	for _, tt := range testCases {
   121  		t.Run(fmt.Sprintf("%q LIKE %q", tt.value, tt.pattern), func(t *testing.T) {
   122  			var escape sql.Expression
   123  			if tt.escape != "" {
   124  				escape = NewLiteral(tt.escape, types.LongText)
   125  			}
   126  			f := NewLike(
   127  				NewGetField(0, types.CreateText(tt.collation), "", false),
   128  				NewGetField(1, types.CreateText(tt.collation), "", false),
   129  				escape,
   130  			)
   131  			value, err := f.Eval(sql.NewEmptyContext(), sql.NewRow(
   132  				tt.value,
   133  				tt.pattern,
   134  			))
   135  			require.NoError(t, err)
   136  			require.Equal(t, tt.ok, value)
   137  		})
   138  	}
   139  }