Skip to content

Commit b919ba0

Browse files
GabeFernandez310GabeFernandez310
authored
Add Alternate Syntax For Match_Query And Other Functions (#1166)
Added Tests And Implementation For Match_Query, Match_Phrase, and Multi_Match Functions Signed-off-by: GabeFernandez310 <gabrielf@bitquilltech.com> Signed-off-by: GabeFernandez310 <gabrielf@bitquilltech.com>
1 parent 438c44d commit b919ba0

9 files changed

Lines changed: 320 additions & 4 deletions

File tree

‎docs/user/dql/functions.rst‎

Lines changed: 55 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3044,6 +3044,16 @@ Another example to show how to set custom values for the optional parameters::
30443044
| Bond |
30453045
+------------+
30463046

3047+
The matchquery function also supports an alternative syntax::
3048+
3049+
os> SELECT firstname FROM accounts WHERE firstname = matchquery('Hattie');
3050+
fetched rows / total rows = 1/1
3051+
+-------------+
3052+
| firstname |
3053+
|-------------|
3054+
| Hattie |
3055+
+-------------+
3056+
30473057
MATCH_QUERY
30483058
-----
30493059

@@ -3073,6 +3083,16 @@ Another example to show how to set custom values for the optional parameters::
30733083
| Bond |
30743084
+------------+
30753085

3086+
The match_query function also supports an alternative syntax::
3087+
3088+
os> SELECT firstname FROM accounts WHERE firstname = match_query('Hattie');
3089+
fetched rows / total rows = 1/1
3090+
+-------------+
3091+
| firstname |
3092+
|-------------|
3093+
| Hattie |
3094+
+-------------+
3095+
30763096

30773097
MATCH_PHRASE
30783098
------------
@@ -3112,6 +3132,23 @@ Another example to show how to set custom values for the optional parameters::
31123132
| Alan Alexander Milne | Winnie-the-Pooh |
31133133
+----------------------+--------------------------+
31143134

3135+
The match_phrase function also supports an alternative syntax::
3136+
3137+
os> SELECT firstname FROM accounts WHERE firstname = match_phrase('Hattie');
3138+
fetched rows / total rows = 1/1
3139+
+-------------+
3140+
| firstname |
3141+
|-------------|
3142+
| Hattie |
3143+
+-------------+
3144+
3145+
os> SELECT firstname FROM accounts WHERE firstname = matchphrase('Hattie');
3146+
fetched rows / total rows = 1/1
3147+
+-------------+
3148+
| firstname |
3149+
|-------------|
3150+
| Hattie |
3151+
+-------------+
31153152

31163153
MATCH_BOOL_PREFIX
31173154
-----
@@ -3255,6 +3292,24 @@ Another example to show how to set custom values for the optional parameters::
32553292
| 1 | The House at Pooh Corner | Alan Alexander Milne |
32563293
+------+--------------------------+----------------------+
32573294

3295+
The multi_match function also supports an alternative syntax::
3296+
3297+
os> SELECT firstname FROM accounts WHERE firstname = multi_match('Hattie');
3298+
fetched rows / total rows = 1/1
3299+
+-------------+
3300+
| firstname |
3301+
|-------------|
3302+
| Hattie |
3303+
+-------------+
3304+
3305+
os> SELECT firstname FROM accounts WHERE firstname = multimatch('Hattie');
3306+
fetched rows / total rows = 1/1
3307+
+-------------+
3308+
| firstname |
3309+
|-------------|
3310+
| Hattie |
3311+
+-------------+
3312+
32583313
SIMPLE_QUERY_STRING
32593314
-------------------
32603315

‎integ-test/src/test/java/org/opensearch/sql/legacy/MethodQueryIT.java‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -54,7 +54,7 @@ public void matchQueryTest() throws IOException {
5454
"select address from %s where address= matchQuery('880 Holmes Lane') limit 3",
5555
TestsConstants.TEST_INDEX_ACCOUNT));
5656
Assert.assertThat(result,
57-
containsString("{\"match\":{\"address\":{\"query\":\"880 Holmes Lane\""));
57+
containsString("{\\\"match\\\":{\\\"address\\\":{\\\"query\\\":\\\"880 Holmes Lane\\\""));
5858
}
5959

6060
/**

‎integ-test/src/test/java/org/opensearch/sql/sql/MatchIT.java‎

Lines changed: 32 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -103,7 +103,7 @@ public void match_query_in_having() throws IOException {
103103
}
104104

105105
@Test
106-
public void alternate_syntaxes_return_the_same_results() throws IOException {
106+
public void match_aliases_return_the_same_results() throws IOException {
107107
String query1 = "SELECT lastname FROM "
108108
+ TEST_INDEX_ACCOUNT + " HAVING match(firstname, 'Nanette')";
109109
JSONObject result1 = executeJdbcRequest(query1);
@@ -116,4 +116,35 @@ public void alternate_syntaxes_return_the_same_results() throws IOException {
116116
assertEquals(result1.getInt("total"), result2.getInt("total"));
117117
assertEquals(result1.getInt("total"), result3.getInt("total"));
118118
}
119+
120+
@Test
121+
public void match_query_alternate_syntax() throws IOException {
122+
JSONObject result = executeJdbcRequest(
123+
"SELECT lastname FROM " + TEST_INDEX_ACCOUNT + " WHERE lastname = match_query('Bates')");
124+
verifySchema(result, schema("lastname", "text"));
125+
verifyDataRows(result, rows("Bates"));
126+
}
127+
128+
@Test
129+
public void matchquery_alternate_syntax() throws IOException {
130+
JSONObject result = executeJdbcRequest(
131+
"SELECT lastname FROM " + TEST_INDEX_ACCOUNT + " WHERE lastname = matchquery('Bates')");
132+
verifySchema(result, schema("lastname", "text"));
133+
verifyDataRows(result, rows("Bates"));
134+
}
135+
136+
@Test
137+
public void match_alternate_syntaxes_return_the_same_results() throws IOException {
138+
String query1 = "SELECT * FROM "
139+
+ TEST_INDEX_ACCOUNT + " WHERE match(firstname, 'Nanette')";
140+
JSONObject result1 = executeJdbcRequest(query1);
141+
String query2 = "SELECT * FROM "
142+
+ TEST_INDEX_ACCOUNT + " WHERE firstname = match_query('Nanette')";
143+
JSONObject result2 = executeJdbcRequest(query2);
144+
String query3 = "SELECT * FROM "
145+
+ TEST_INDEX_ACCOUNT + " WHERE firstname = matchquery('Nanette')";
146+
JSONObject result3 = executeJdbcRequest(query3);
147+
assertEquals(result1.getInt("total"), result2.getInt("total"));
148+
assertEquals(result1.getInt("total"), result3.getInt("total"));
149+
}
119150
}

‎integ-test/src/test/java/org/opensearch/sql/sql/MatchPhraseIT.java‎

Lines changed: 27 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -51,7 +51,7 @@ public void test_match_phrase_with_slop() throws IOException {
5151
}
5252

5353
@Test
54-
public void test_alternate_syntax_for_match_phrase_returns_same_result() throws IOException {
54+
public void test_aliases_for_match_phrase_returns_same_result() throws IOException {
5555
String query1 = "SELECT phrase FROM %s WHERE matchphrase(phrase, 'quick fox')";
5656
String query2 = "SELECT phrase FROM %s WHERE match_phrase(phrase, 'quick fox')";
5757
String query3 = "SELECT phrase FROM %s WHERE matchphrasequery(phrase, 'quick fox')";
@@ -61,4 +61,30 @@ public void test_alternate_syntax_for_match_phrase_returns_same_result() throws
6161
assertTrue(result1.similar(result2));
6262
assertTrue(result1.similar(result3));
6363
}
64+
65+
@Test
66+
public void match_phrase_alternate_syntax() throws IOException {
67+
String query = "SELECT phrase FROM %s WHERE phrase = match_phrase('quick fox')";
68+
JSONObject result = executeJdbcRequest(String.format(query, TEST_INDEX_PHRASE));
69+
verifyDataRows(result, rows("quick fox"), rows("quick fox here"));
70+
}
71+
72+
@Test
73+
public void matchphrase_alternate_syntax() throws IOException {
74+
String query = "SELECT phrase FROM %s WHERE phrase = matchphrase('quick fox')";
75+
JSONObject result = executeJdbcRequest(String.format(query, TEST_INDEX_PHRASE));
76+
verifyDataRows(result, rows("quick fox"), rows("quick fox here"));
77+
}
78+
79+
@Test
80+
public void match_phrase_alternate_syntaxes_return_the_same_results() throws IOException {
81+
String query1 = "SELECT phrase FROM %s WHERE matchphrase(phrase, 'quick fox')";
82+
String query2 = "SELECT phrase FROM %s WHERE phrase = matchphrase('quick fox')";
83+
String query3 = "SELECT phrase FROM %s WHERE phrase = match_phrase('quick fox')";
84+
JSONObject result1 = executeJdbcRequest(String.format(query1, TEST_INDEX_PHRASE));
85+
JSONObject result2 = executeJdbcRequest(String.format(query2, TEST_INDEX_PHRASE));
86+
JSONObject result3 = executeJdbcRequest(String.format(query3, TEST_INDEX_PHRASE));
87+
assertTrue(result1.similar(result2));
88+
assertTrue(result1.similar(result3));
89+
}
6490
}

‎integ-test/src/test/java/org/opensearch/sql/sql/MultiMatchIT.java‎

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -124,4 +124,35 @@ public void test_all_params_multimatchquery_alternate_parameter_syntax() {
124124
JSONObject result = executeJdbcRequest(query);
125125
assertEquals(2, result.getInt("total"));
126126
}
127+
128+
@Test
129+
public void multi_match_alternate_syntax() throws IOException {
130+
String query = "SELECT Id FROM " + TEST_INDEX_BEER
131+
+ " WHERE CreationDate = multi_match('2014-01-22');";
132+
var result = new JSONObject(executeQuery(query, "jdbc"));
133+
assertEquals(8, result.getInt("total"));
134+
}
135+
136+
@Test
137+
public void multimatch_alternate_syntax() throws IOException {
138+
String query = "SELECT Id FROM " + TEST_INDEX_BEER
139+
+ " WHERE CreationDate = multimatch('2014-01-22');";
140+
var result = new JSONObject(executeQuery(query, "jdbc"));
141+
assertEquals(8, result.getInt("total"));
142+
}
143+
144+
@Test
145+
public void multi_match_alternate_syntaxes_return_the_same_results() throws IOException {
146+
String query1 = "SELECT Id FROM " + TEST_INDEX_BEER
147+
+ " WHERE multi_match(['CreationDate'], '2014-01-22');";
148+
String query2 = "SELECT Id FROM " + TEST_INDEX_BEER
149+
+ " WHERE CreationDate = multi_match('2014-01-22');";
150+
String query3 = "SELECT Id FROM " + TEST_INDEX_BEER
151+
+ " WHERE CreationDate = multimatch('2014-01-22');";
152+
var result1 = new JSONObject(executeQuery(query1, "jdbc"));
153+
var result2 = new JSONObject(executeQuery(query2, "jdbc"));
154+
var result3 = new JSONObject(executeQuery(query3, "jdbc"));
155+
assertEquals(result1.getInt("total"), result2.getInt("total"));
156+
assertEquals(result1.getInt("total"), result3.getInt("total"));
157+
}
127158
}

‎sql/src/main/antlr/OpenSearchSQLParser.g4‎

Lines changed: 26 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -323,6 +323,10 @@ positionFunction
323323
: POSITION LR_BRACKET functionArg IN functionArg RR_BRACKET
324324
;
325325

326+
matchQueryAltSyntaxFunction
327+
: field=relevanceField EQUAL_SYMBOL MATCH_QUERY LR_BRACKET query=relevanceQuery RR_BRACKET
328+
;
329+
326330
scalarFunctionName
327331
: mathematicalFunctionName
328332
| dateTimeFunctionName
@@ -340,7 +344,8 @@ specificFunction
340344
;
341345

342346
relevanceFunction
343-
: noFieldRelevanceFunction | singleFieldRelevanceFunction | multiFieldRelevanceFunction
347+
: noFieldRelevanceFunction | singleFieldRelevanceFunction | multiFieldRelevanceFunction | altSingleFieldRelevanceFunction | altMultiFieldRelevanceFunction
348+
344349
;
345350

346351
noFieldRelevanceFunction
@@ -362,6 +367,14 @@ multiFieldRelevanceFunction
362367
alternateMultiMatchQuery COMMA alternateMultiMatchField (COMMA relevanceArg)* RR_BRACKET
363368
;
364369

370+
altSingleFieldRelevanceFunction
371+
: field=relevanceField EQUAL_SYMBOL altSyntaxFunctionName=altSingleFieldRelevanceFunctionName LR_BRACKET query=relevanceQuery (COMMA relevanceArg)* RR_BRACKET
372+
;
373+
374+
altMultiFieldRelevanceFunction
375+
: field=relevanceField EQUAL_SYMBOL altSyntaxFunctionName=altMultiFieldRelevanceFunctionName LR_BRACKET query=relevanceQuery (COMMA relevanceArg)* RR_BRACKET
376+
;
377+
365378
convertedDataType
366379
: typeName=DATE
367380
| typeName=TIME
@@ -482,6 +495,18 @@ multiFieldRelevanceFunctionName
482495
| QUERY_STRING
483496
;
484497

498+
altSingleFieldRelevanceFunctionName
499+
: MATCH_QUERY
500+
| MATCHQUERY
501+
| MATCH_PHRASE
502+
| MATCHPHRASE
503+
;
504+
505+
altMultiFieldRelevanceFunctionName
506+
: MULTI_MATCH
507+
| MULTIMATCH
508+
;
509+
485510
functionArgs
486511
: (functionArg (COMMA functionArg)*)?
487512
;

‎sql/src/main/java/org/opensearch/sql/sql/parser/AstExpressionBuilder.java‎

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -429,6 +429,14 @@ public UnresolvedExpression visitSingleFieldRelevanceFunction(
429429
singleFieldRelevanceArguments(ctx));
430430
}
431431

432+
@Override
433+
public UnresolvedExpression visitAltSingleFieldRelevanceFunction(
434+
OpenSearchSQLParser.AltSingleFieldRelevanceFunctionContext ctx) {
435+
return new Function(
436+
ctx.altSyntaxFunctionName.getText().toLowerCase(),
437+
altSingleFieldRelevanceFunctionArguments(ctx));
438+
}
439+
432440
@Override
433441
public UnresolvedExpression visitMultiFieldRelevanceFunction(
434442
MultiFieldRelevanceFunctionContext ctx) {
@@ -450,6 +458,14 @@ public UnresolvedExpression visitMultiFieldRelevanceFunction(
450458
}
451459
}
452460

461+
@Override
462+
public UnresolvedExpression visitAltMultiFieldRelevanceFunction(
463+
OpenSearchSQLParser.AltMultiFieldRelevanceFunctionContext ctx) {
464+
return new Function(
465+
ctx.altSyntaxFunctionName.getText().toLowerCase(),
466+
altMultiFieldRelevanceFunctionArguments(ctx));
467+
}
468+
453469
private Function buildFunction(String functionName,
454470
List<FunctionArgContext> arg) {
455471
return new Function(
@@ -506,6 +522,18 @@ private List<UnresolvedExpression> singleFieldRelevanceArguments(
506522
}
507523

508524

525+
private List<UnresolvedExpression> altSingleFieldRelevanceFunctionArguments(
526+
OpenSearchSQLParser.AltSingleFieldRelevanceFunctionContext ctx) {
527+
// all the arguments are defaulted to string values
528+
// to skip environment resolving and function signature resolving
529+
ImmutableList.Builder<UnresolvedExpression> builder = ImmutableList.builder();
530+
builder.add(new UnresolvedArgument("field",
531+
new Literal(StringUtils.unquoteText(ctx.field.getText()), DataType.STRING)));
532+
builder.add(new UnresolvedArgument("query",
533+
new Literal(StringUtils.unquoteText(ctx.query.getText()), DataType.STRING)));
534+
fillRelevanceArgs(ctx.relevanceArg(), builder);
535+
return builder.build();
536+
}
509537

510538
private List<UnresolvedExpression> multiFieldRelevanceArguments(
511539
MultiFieldRelevanceFunctionContext ctx) {
@@ -561,4 +589,19 @@ private List<UnresolvedExpression> alternateMultiMatchArguments(
561589

562590
return builder.build();
563591
}
592+
593+
private List<UnresolvedExpression> altMultiFieldRelevanceFunctionArguments(
594+
OpenSearchSQLParser.AltMultiFieldRelevanceFunctionContext ctx) {
595+
// all the arguments are defaulted to string values
596+
// to skip environment resolving and function signature resolving
597+
var map = new HashMap<String, Float>();
598+
map.put(ctx.field.getText(), 1F);
599+
ImmutableList.Builder<UnresolvedExpression> builder = ImmutableList.builder();
600+
var fields = new RelevanceFieldList(map);
601+
builder.add(new UnresolvedArgument("fields", fields));
602+
builder.add(new UnresolvedArgument("query",
603+
new Literal(StringUtils.unquoteText(ctx.query.getText()), DataType.STRING)));
604+
fillRelevanceArgs(ctx.relevanceArg(), builder);
605+
return builder.build();
606+
}
564607
}

‎sql/src/test/java/org/opensearch/sql/sql/antlr/SQLSyntaxParserTest.java‎

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -542,6 +542,30 @@ private static Stream<String> matchPhraseComplexQueries() {
542542
);
543543
}
544544

545+
@Test
546+
public void canParseMatchQueryAlternateSyntax() {
547+
assertNotNull(parser.parse("SELECT * FROM test WHERE Field = matchquery('query')"));
548+
assertNotNull(parser.parse("SELECT * FROM test WHERE Field = matchquery(\"query\")"));
549+
assertNotNull(parser.parse("SELECT * FROM test WHERE Field = match_query('query')"));
550+
assertNotNull(parser.parse("SELECT * FROM test WHERE Field = match_query(\"query\")"));
551+
}
552+
553+
@Test
554+
public void canParseMatchPhraseAlternateSyntax() {
555+
assertNotNull(parser.parse("SELECT * FROM test WHERE Field = match_phrase('query')"));
556+
assertNotNull(parser.parse("SELECT * FROM test WHERE Field = match_phrase(\"query\")"));
557+
assertNotNull(parser.parse("SELECT * FROM test WHERE Field = matchphrase('query')"));
558+
assertNotNull(parser.parse("SELECT * FROM test WHERE Field = matchphrase(\"query\")"));
559+
}
560+
561+
@Test
562+
public void canParseMultiMatchAlternateSyntax() {
563+
assertNotNull(parser.parse("SELECT * FROM test WHERE Field = multi_match('query')"));
564+
assertNotNull(parser.parse("SELECT * FROM test WHERE Field = multi_match(\"query\")"));
565+
assertNotNull(parser.parse("SELECT * FROM test WHERE Field = multimatch('query')"));
566+
assertNotNull(parser.parse("SELECT * FROM test WHERE Field = multimatch(\"query\")"));
567+
}
568+
545569
private static Stream<String> matchPhraseQueryComplexQueries() {
546570
return Stream.of(
547571
"SELECT * FROM t WHERE matchphrasequery(c, 3)",

0 commit comments

Comments
 (0)