Match query is not working in sql dev tools while its working in query workbench in opensearch

[Shivacharangoud]

SELECT * FROM contacts WHERE match(name, 'charan') ;

The above query is working in query workbench
But if I use the same query in dev tools. Using plugin not working
Example :
POST _plugins/_sql
{
"query":"SELECT * FROM contacts WHERE match(name, 'charan') ;"
}

The above one not working.
Can any help me out with this?

[dai-chen]

@Shivacharangoud Thanks for reporting the issue! Could you provide more details? Such as your test index, OpenSearch version etc.

[Shivacharangoud]

version:OpenSearch 1.2
POST _plugins/_sql?format=json
{
"query":"SELECT * FROM contacts WHERE match(name, 'charan') ;"
}
if i run the above script iam getting this error
{
"error": {
"reason": "Invalid SQL query",
"details": "syntax error, expect AGAINST, actual EOF",
"type": "ParserException"
},
"status": 400
}

[dai-chen]

version:OpenSearch 1.2 POST _plugins/_sql?format=json { "query":"SELECT * FROM contacts WHERE match(name, 'charan') ;" } if i run the above script iam getting this error { "error": { "reason": "Invalid SQL query", "details": "syntax error, expect AGAINST, actual EOF", "type": "ParserException" }, "status": 400 }

Could you share the index mapping and some test data? Error stacktrace would be also helpful. Thanks!

[Shivacharangoud]

mappings:

{
  "contacts" : {
    "mappings" : {
      "properties" : {
        "age" : {
          "type" : "integer"
        },
        "name" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword"
            }
          }
        }
      }
    }
  }
}

available data in index:

{
  "took" : 3,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 1,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "contacts",
        "_type" : "_doc",
        "_id" : "gKrBy4IBNenCx7xvg_2M",
        "_score" : 1.0,
        "_source" : {
          "name" : "charan",
          "age" : 22
        }
      }
    ]
  }
}

[Shivacharangoud]

i have an question which is not related to this issues.
can we add two indices using DSL on common field in opensearch? if yes how we can do it.
please answer to this.

[bbarani]

@dai-chen Removed the old version label from this issue. Please re-tag this issue with appropriate version.

[dai-chen]

@dai-chen Removed the old version label from this issue. Please re-tag this issue with appropriate version.

Will do. Thanks!

[dai-chen]

@Shivacharangoud We've added more complete support for full text search functions including match: #182. Feel free to reopen if you still face this issue. Thanks!

[ghost]

@dai-chen this seems to be broken when using JSON format type with match. CSV and standard responses work fine.

running query: SELECT * FROM test-index WHERE match(TEST_COLUMN, 'test')

getting syntax error, expect AGAINST, actual EOF

When tweaking to match MySQL standard with expected against

SELECT * FROM test-index WHERE MATCH(TEST_COLUMN) AGAINST ('test')

I am getting

err find condition class com.alibaba.druid.sql.dialect.mysql.ast.expr.MySqlMatchAgainstExpr

Any ideas?

[dai-chen]

@EfChouTR From what you posted, the query was fallback to our legacy code. Just to confirm you ran each query with format=json in request URL right?

@acarbonetto Can someone help take a look? I forgot if we support relevancy search function with JSON format in v2 engine. Thanks!

[ghost]

@EfChouTR From what you posted, the query was fallback to our legacy code. Just to confirm you ran each query with format=json in request URL right?

Correct. It will be helpful to know this ahead of time in the documentation, so we know what's supported and what isn't in each format type.

[Yury-Fridlyand]

JSON format in v2 engine

... isn't supported @dai-chen. You can track it in #1450