go.temporal.io/server@v1.23.0/common/persistence/visibility/store/sql/query_converter_postgresql_test.go (about)

     1  // The MIT License
     2  //
     3  // Copyright (c) 2020 Temporal Technologies Inc.  All rights reserved.
     4  //
     5  // Copyright (c) 2020 Uber Technologies, Inc.
     6  //
     7  // Permission is hereby granted, free of charge, to any person obtaining a copy
     8  // of this software and associated documentation files (the "Software"), to deal
     9  // in the Software without restriction, including without limitation the rights
    10  // to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
    11  // copies of the Software, and to permit persons to whom the Software is
    12  // furnished to do so, subject to the following conditions:
    13  //
    14  // The above copyright notice and this permission notice shall be included in
    15  // all copies or substantial portions of the Software.
    16  //
    17  // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
    18  // IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
    19  // FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
    20  // AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
    21  // LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
    22  // OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
    23  // THE SOFTWARE.
    24  
    25  package sql
    26  
    27  import (
    28  	"fmt"
    29  	"testing"
    30  
    31  	"github.com/stretchr/testify/suite"
    32  	"github.com/temporalio/sqlparser"
    33  
    34  	"go.temporal.io/server/common/persistence/visibility/store/query"
    35  )
    36  
    37  type (
    38  	postgresqlQueryConverterSuite struct {
    39  		queryConverterSuite
    40  	}
    41  )
    42  
    43  func TestPostgreSQLQueryConverterSuite(t *testing.T) {
    44  	s := &postgresqlQueryConverterSuite{
    45  		queryConverterSuite: queryConverterSuite{
    46  			pqc: &pgQueryConverter{},
    47  		},
    48  	}
    49  	suite.Run(t, s)
    50  }
    51  
    52  func (s *postgresqlQueryConverterSuite) TestGetCoalesceCloseTimeExpr() {
    53  	expr := s.queryConverter.getCoalesceCloseTimeExpr()
    54  	s.Equal(
    55  		"coalesce(close_time, '9999-12-31 23:59:59')",
    56  		sqlparser.String(expr),
    57  	)
    58  }
    59  
    60  func (s *postgresqlQueryConverterSuite) TestConvertKeywordListComparisonExpr() {
    61  	var tests = []testCase{
    62  		{
    63  			name:   "invalid operator",
    64  			input:  "AliasForKeywordList01 < 'foo'",
    65  			output: "",
    66  			err: query.NewConverterError(
    67  				"%s: operator '%s' not supported for KeywordList type search attribute in `%s`",
    68  				query.InvalidExpressionErrMessage,
    69  				sqlparser.LessThanStr,
    70  				"AliasForKeywordList01 < 'foo'",
    71  			),
    72  		},
    73  		{
    74  			name:   "valid equal expression",
    75  			input:  "AliasForKeywordList01 = 'foo'",
    76  			output: "KeywordList01 @> jsonb_build_array('foo')",
    77  			err:    nil,
    78  		},
    79  		{
    80  			name:   "valid not equal expression",
    81  			input:  "AliasForKeywordList01 != 'foo'",
    82  			output: "not KeywordList01 @> jsonb_build_array('foo')",
    83  			err:    nil,
    84  		},
    85  		{
    86  			name:   "valid in expression",
    87  			input:  "AliasForKeywordList01 in ('foo', 'bar')",
    88  			output: "(KeywordList01 @> jsonb_build_array('foo') or KeywordList01 @> jsonb_build_array('bar'))",
    89  			err:    nil,
    90  		},
    91  		{
    92  			name:   "valid not in expression",
    93  			input:  "AliasForKeywordList01 not in ('foo', 'bar')",
    94  			output: "not (KeywordList01 @> jsonb_build_array('foo') or KeywordList01 @> jsonb_build_array('bar'))",
    95  			err:    nil,
    96  		},
    97  	}
    98  
    99  	for _, tc := range tests {
   100  		s.Run(tc.name, func() {
   101  			sql := fmt.Sprintf("select * from table1 where %s", tc.input)
   102  			stmt, err := sqlparser.Parse(sql)
   103  			s.NoError(err)
   104  			expr := stmt.(*sqlparser.Select).Where.Expr
   105  			err = s.queryConverter.convertComparisonExpr(&expr)
   106  			if tc.err == nil {
   107  				s.NoError(err)
   108  				s.Equal(tc.output, sqlparser.String(expr))
   109  			} else {
   110  				s.Error(err)
   111  				s.Equal(err, tc.err)
   112  			}
   113  		})
   114  	}
   115  }
   116  
   117  func (s *postgresqlQueryConverterSuite) TestConvertTextComparisonExpr() {
   118  	var tests = []testCase{
   119  		{
   120  			name:   "invalid operator",
   121  			input:  "AliasForText01 < 'foo'",
   122  			output: "",
   123  			err: query.NewConverterError(
   124  				"%s: operator '%s' not supported for Text type search attribute in `%s`",
   125  				query.InvalidExpressionErrMessage,
   126  				sqlparser.LessThanStr,
   127  				"AliasForText01 < 'foo'",
   128  			),
   129  		},
   130  		{
   131  			name:   "valid equal expression",
   132  			input:  "AliasForText01 = 'foo bar'",
   133  			output: "Text01 @@ 'foo | bar'::tsquery",
   134  			err:    nil,
   135  		},
   136  		{
   137  			name:   "valid not equal expression",
   138  			input:  "AliasForText01 != 'foo bar'",
   139  			output: "not Text01 @@ 'foo | bar'::tsquery",
   140  			err:    nil,
   141  		},
   142  	}
   143  
   144  	for _, tc := range tests {
   145  		s.Run(tc.name, func() {
   146  			sql := fmt.Sprintf("select * from table1 where %s", tc.input)
   147  			stmt, err := sqlparser.Parse(sql)
   148  			s.NoError(err)
   149  			expr := stmt.(*sqlparser.Select).Where.Expr
   150  			err = s.queryConverter.convertComparisonExpr(&expr)
   151  			if tc.err == nil {
   152  				s.NoError(err)
   153  				s.Equal(tc.output, sqlparser.String(expr))
   154  			} else {
   155  				s.Error(err)
   156  				s.Equal(err, tc.err)
   157  			}
   158  		})
   159  	}
   160  }