go.temporal.io/server@v1.23.0/common/persistence/visibility/store/elasticsearch/converter_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 // Copyright (c) 2017 Xargin 8 // 9 // Permission is hereby granted, free of charge, to any person obtaining a copy 10 // of this software and associated documentation files (the "Software"), to deal 11 // in the Software without restriction, including without limitation the rights 12 // to use, copy, modify, merge, publish, distribute, sublicense, and/or sell 13 // copies of the Software, and to permit persons to whom the Software is 14 // furnished to do so, subject to the following conditions: 15 // 16 // The above copyright notice and this permission notice shall be included in 17 // all copies or substantial portions of the Software. 18 // 19 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR 20 // IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, 21 // FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE 22 // AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER 23 // LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, 24 // OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN 25 // THE SOFTWARE. 26 27 package elasticsearch 28 29 import ( 30 "encoding/json" 31 "fmt" 32 "testing" 33 34 "github.com/stretchr/testify/assert" 35 36 "go.temporal.io/server/common/persistence/visibility/store/query" 37 ) 38 39 var errorCases = map[string]string{ 40 "delete": query.MalformedSqlQueryErrMessage, 41 "update x": query.MalformedSqlQueryErrMessage, 42 "insert ": query.MalformedSqlQueryErrMessage, 43 "insert into a values(1,2)": query.NotSupportedErrMessage, 44 "update a set id = 1": query.NotSupportedErrMessage, 45 "delete from a where id=1": query.NotSupportedErrMessage, 46 "select * from a where NOT(id=1)": query.NotSupportedErrMessage, 47 "select * from a where 1 = 1": query.InvalidExpressionErrMessage, 48 "select * from a where 1=a": query.InvalidExpressionErrMessage, 49 "select * from a where zz(k=2)": query.NotSupportedErrMessage, 50 "select * from a group by k, m": query.NotSupportedErrMessage, 51 "select * from a group by k order by id": query.NotSupportedErrMessage, 52 "invalid query": query.MalformedSqlQueryErrMessage, 53 "select * from a where a= 1 and multi_match(zz=1, query='this is a test', fields=(title,title.origin), type=phrase)": query.NotSupportedErrMessage, 54 } 55 56 var supportedWhereCases = map[string]string{ 57 "process_id= 1": `{"bool":{"filter":{"match":{"process_id":{"query":1}}}}}`, 58 "(process_id= 1)": `{"bool":{"filter":{"match":{"process_id":{"query":1}}}}}`, 59 "((process_id= 1))": `{"bool":{"filter":{"match":{"process_id":{"query":1}}}}}`, 60 "(process_id = 1 and status=1)": `{"bool":{"filter":[{"match":{"process_id":{"query":1}}},{"match":{"status":{"query":1}}}]}}`, 61 "`status`=1": `{"bool":{"filter":{"match":{"status":{"query":1}}}}}`, 62 "process_id > 1": `{"bool":{"filter":{"range":{"process_id":{"from":1,"include_lower":false,"include_upper":true,"to":null}}}}}`, 63 "process_id < 1": `{"bool":{"filter":{"range":{"process_id":{"from":null,"include_lower":true,"include_upper":false,"to":1}}}}}`, 64 "process_id <= 1": `{"bool":{"filter":{"range":{"process_id":{"from":null,"include_lower":true,"include_upper":true,"to":1}}}}}`, 65 "process_id >= 1": `{"bool":{"filter":{"range":{"process_id":{"from":1,"include_lower":true,"include_upper":true,"to":null}}}}}`, 66 "process_id != 1": `{"bool":{"must_not":{"match":{"process_id":{"query":1}}}}}`, 67 "process_id = 0 and status= 1 and channel = 4": `{"bool":{"filter":[{"match":{"process_id":{"query":0}}},{"match":{"status":{"query":1}}},{"match":{"channel":{"query":4}}}]}}`, 68 "process_id > 1 and status = 1": `{"bool":{"filter":[{"range":{"process_id":{"from":1,"include_lower":false,"include_upper":true,"to":null}}},{"match":{"status":{"query":1}}}]}}`, 69 "id > 1 or process_id = 0": `{"bool":{"should":[{"range":{"id":{"from":1,"include_lower":false,"include_upper":true,"to":null}}},{"match":{"process_id":{"query":0}}}]}}`, 70 "id > 1 and d = 1 or process_id = 0 and x = 2": `{"bool":{"should":[{"bool":{"filter":[{"range":{"id":{"from":1,"include_lower":false,"include_upper":true,"to":null}}},{"match":{"d":{"query":1}}}]}},{"bool":{"filter":[{"match":{"process_id":{"query":0}}},{"match":{"x":{"query":2}}}]}}]}}`, 71 "(id > 1 and d = 1)": `{"bool":{"filter":[{"range":{"id":{"from":1,"include_lower":false,"include_upper":true,"to":null}}},{"match":{"d":{"query":1}}}]}}`, 72 "(id > 1 and d = 1) or (c=1)": `{"bool":{"should":[{"bool":{"filter":[{"range":{"id":{"from":1,"include_lower":false,"include_upper":true,"to":null}}},{"match":{"d":{"query":1}}}]}},{"match":{"c":{"query":1}}}]}}`, 73 "nid=1 and (cif = 1 or cif = 2)": `{"bool":{"filter":[{"match":{"nid":{"query":1}}},{"bool":{"should":[{"match":{"cif":{"query":1}}},{"match":{"cif":{"query":2}}}]}}]}}`, 74 "id > 1 or (process_id = 0)": `{"bool":{"should":[{"range":{"id":{"from":1,"include_lower":false,"include_upper":true,"to":null}}},{"match":{"process_id":{"query":0}}}]}}`, 75 "id in (1,2,3,4)": `{"bool":{"filter":{"terms":{"id":[1,2,3,4]}}}}`, 76 "a = 'text'": `{"bool":{"filter":{"match":{"a":{"query":"text"}}}}}`, 77 "a LiKE '%a%'": `{"bool":{"filter":{"match":{"a":{"query":"a"}}}}}`, 78 "`by` = 1": `{"bool":{"filter":{"match":{"by":{"query":1}}}}}`, 79 "id not like '%aaa%'": `{"bool":{"must_not":{"match":{"id":{"query":"aaa"}}}}}`, 80 "id not IN (1, 2,3)": `{"bool":{"must_not":{"terms":{"id":[1,2,3]}}}}`, 81 "id iS not null": `{"bool":{"filter":{"exists":{"field":"id"}}}}`, 82 "id is NULL": `{"bool":{"must_not":{"exists":{"field":"id"}}}}`, 83 "value = '1'": `{"bool":{"filter":{"match":{"value":{"query":"1"}}}}}`, 84 "value = 'true'": `{"bool":{"filter":{"match":{"value":{"query":"true"}}}}}`, 85 "value = 'True'": `{"bool":{"filter":{"match":{"value":{"query":"True"}}}}}`, 86 "value = true": `{"bool":{"filter":{"match":{"value":{"query":true}}}}}`, 87 "value = True": `{"bool":{"filter":{"match":{"value":{"query":true}}}}}`, 88 "value = 1528358645123456789": `{"bool":{"filter":{"match":{"value":{"query":1528358645123456789}}}}}`, 89 "value = 1528358645.1234567": `{"bool":{"filter":{"match":{"value":{"query":1528358645.1234567}}}}}`, 90 // Long float is truncated. 91 "value = 1528358645.123456790": `{"bool":{"filter":{"match":{"value":{"query":1528358645.1234567}}}}}`, 92 "id in (\"text1\",'text2') and content = 'aaaa'": `{"bool":{"filter":[{"terms":{"id":["text1","text2"]}},{"match":{"content":{"query":"aaaa"}}}]}}`, 93 "create_time BETWEEN '2015-01-01 00:00:00' and '2016-02-02 00:00:00'": `{"bool":{"filter":{"range":{"create_time":{"from":"2015-01-01 00:00:00","include_lower":true,"include_upper":true,"to":"2016-02-02 00:00:00"}}}}}`, 94 "create_time nOt between '2015-01-01 00:00:00' and '2016-02-02 00:00:00'": `{"bool":{"must_not":{"range":{"create_time":{"from":"2015-01-01 00:00:00","include_lower":true,"include_upper":true,"to":"2016-02-02 00:00:00"}}}}}`, 95 "create_time between '2015-01-01T00:00:00+0800' and '2017-01-01T00:00:00+0800' and process_id = 0 and status >= 1 and content = '三个男人' and phone = '15810324322'": `{"bool":{"filter":[{"range":{"create_time":{"from":"2015-01-01T00:00:00+0800","include_lower":true,"include_upper":true,"to":"2017-01-01T00:00:00+0800"}}},{"match":{"process_id":{"query":0}}},{"range":{"status":{"from":1,"include_lower":true,"include_upper":true,"to":null}}},{"match":{"content":{"query":"三个男人"}}},{"match":{"phone":{"query":"15810324322"}}}]}}`, 96 "value starts_with 'prefix'": `{"bool":{"filter":{"prefix":{"value":"prefix"}}}}`, 97 "value not starts_with 'prefix'": `{"bool":{"must_not":{"prefix":{"value":"prefix"}}}}`, 98 } 99 100 var supportedWhereOrderCases = map[string]struct { 101 query string 102 sorter string 103 }{ 104 "id > 1 order by id asc, order_id desc": { 105 query: `{"bool":{"filter":{"range":{"id":{"from":1,"include_lower":false,"include_upper":true,"to":null}}}}}`, 106 sorter: `[{"id":{"order":"asc"}},{"order_id":{"order":"desc"}}]`, 107 }, 108 "id is null order by `order`.abc": { 109 query: `{"bool":{"must_not":{"exists":{"field":"id"}}}}`, 110 sorter: `[{"order.abc":{"order":"asc"}}]`, 111 }, 112 "id beTweeN 1 AnD 3 ORdeR BY random_id DESC": { 113 query: `{"bool":{"filter":{"range":{"id":{"from":1,"include_lower":true,"include_upper":true,"to":3}}}}}`, 114 sorter: `[{"random_id":{"order":"desc"}}]`, 115 }, 116 } 117 118 var supportedWhereGroupByCases = map[string]struct { 119 query string 120 groupBy []string 121 }{ 122 "group by status": { 123 query: ``, 124 groupBy: []string{"status"}, 125 }, 126 "id = 1 group by status": { 127 query: `{"bool":{"filter":{"match":{"id":{"query":1}}}}}`, 128 groupBy: []string{"status"}, 129 }, 130 } 131 132 func TestSupportedSelectWhere(t *testing.T) { 133 c := newQueryConverter(nil, nil) 134 135 for sql, expectedJson := range supportedWhereCases { 136 queryParams, err := c.ConvertWhereOrderBy(sql) 137 assert.NoError(t, err) 138 139 actualMap, _ := queryParams.Query.Source() 140 actualJson, _ := json.Marshal(actualMap) 141 142 assert.Equal(t, expectedJson, string(actualJson), fmt.Sprintf("sql: %s", sql)) 143 } 144 } 145 146 func TestEmptySelectWhere(t *testing.T) { 147 c := newQueryConverter(nil, nil) 148 149 queryParams, err := c.ConvertWhereOrderBy("") 150 assert.NoError(t, err) 151 assert.Nil(t, queryParams.Query) 152 assert.Nil(t, queryParams.Sorter) 153 154 queryParams, err = c.ConvertWhereOrderBy("order by Id desc") 155 assert.NoError(t, err) 156 assert.Nil(t, queryParams.Query) 157 assert.Len(t, queryParams.Sorter, 1) 158 actualSorterMap, _ := queryParams.Sorter[0].Source() 159 actualSorterJson, _ := json.Marshal([]interface{}{actualSorterMap}) 160 assert.Equal(t, `[{"Id":{"order":"desc"}}]`, string(actualSorterJson)) 161 } 162 163 func TestSupportedSelectWhereOrder(t *testing.T) { 164 c := newQueryConverter(nil, nil) 165 166 for sql, expectedJson := range supportedWhereOrderCases { 167 queryParams, err := c.ConvertWhereOrderBy(sql) 168 assert.NoError(t, err) 169 170 actualQueryMap, _ := queryParams.Query.Source() 171 actualQueryJson, _ := json.Marshal(actualQueryMap) 172 assert.Equal(t, expectedJson.query, string(actualQueryJson), fmt.Sprintf("sql: %s", sql)) 173 174 var actualSorterMaps []interface{} 175 for _, sorter := range queryParams.Sorter { 176 actualSorterMap, _ := sorter.Source() 177 actualSorterMaps = append(actualSorterMaps, actualSorterMap) 178 } 179 actualSorterJson, _ := json.Marshal(actualSorterMaps) 180 assert.Equal(t, expectedJson.sorter, string(actualSorterJson), fmt.Sprintf("sql: %s", sql)) 181 } 182 } 183 184 func TestSupportedSelectWhereGroupBy(t *testing.T) { 185 c := newQueryConverter(nil, nil) 186 187 for sql, expectedJson := range supportedWhereGroupByCases { 188 queryParams, err := c.ConvertWhereOrderBy(sql) 189 assert.NoError(t, err) 190 191 if expectedJson.query != "" { 192 actualQueryMap, _ := queryParams.Query.Source() 193 actualQueryJson, _ := json.Marshal(actualQueryMap) 194 assert.Equal(t, expectedJson.query, string(actualQueryJson), fmt.Sprintf("sql: %s", sql)) 195 } else { 196 assert.Nil(t, queryParams.Query) 197 } 198 assert.Equal(t, expectedJson.groupBy, queryParams.GroupBy) 199 } 200 } 201 202 func TestErrors(t *testing.T) { 203 c := newQueryConverter(nil, nil) 204 for sql, expectedErrMessage := range errorCases { 205 _, err := c.ConvertSql(sql) 206 assert.Contains(t, err.Error(), expectedErrMessage, sql) 207 } 208 }