SQL Syntax Issue

[mlrlite]

I am submitting the following to my elasticsearch instance. While my local docker instance returns my records starting with graphic, the AWS Open Distro returns me any records that contain graphic. Is this something that can be addressed or work around provided.

Sample Query
query="SELECT * FROM company_1 WHERE name LIKE 'graphic%'"

Thank You

[aetter]

Hi @mlrlite, can you clarify the versions in each situation? In one situation, it sounds like you're running the ODFE Docker image locally. Which version?

In the other situation, when you say "AWS Open Distro," do you mean Amazon Elasticsearch Service? One of the RPM or Debian packages on EC2? Something else? Which version?

The SQL plugin is constantly evolving, so I wouldn't be surprised if this was a bug in older versions that's since been fixed.

[mlrlite]

My local has the following

    "cluster_name": "docker-cluster",
    "cluster_uuid": "9YSYZx8PSP6mbzSR4MaflA",
    "name": "12b8b4ddfaa3",
    "tagline": "You Know, for Search",
    "version": {
        "build_date": "2019-09-06T14:40:30.409026Z",
        "build_flavor": "default",
        "build_hash": "1c1faf1",
        "build_snapshot": false,
        "build_type": "docker",
        "lucene_version": "8.1.0",
        "minimum_index_compatibility_version": "6.0.0-beta1",
        "minimum_wire_compatibility_version": "6.8.0",
        "number": "7.3.2"
    }
}```

My aws service is using:
```{
  "name" : "dcb6310d7bf62ea71ad53d4a79c3687d",
  "cluster_name" : "279625311674:spendhq-es-search-demo",
  "cluster_uuid" : "AcZ9u2aAQDmcY-J7mpvlkg",
  "version" : {
    "number" : "7.1.1",
    "build_flavor" : "oss",
    "build_type" : "tar",
    "build_hash" : "7a013de",
    "build_date" : "2019-09-05T07:25:23.525600Z",
    "build_snapshot" : false,
    "lucene_version" : "8.0.0",
    "minimum_wire_compatibility_version" : "6.8.0",
    "minimum_index_compatibility_version" : "6.0.0-beta1"
  },
  "tagline" : "You Know, for Search"
}

Please let me know if there is anything else I can provide.

[aetter]

Hi @mlrlite, it looks like your Docker cluster is running the "default" version of Elasticsearch, which doesn't have the ODFE plugins and instead has a proprietary SQL plugin. Did you install the ODFE SQL plugin manually on the Docker image? If you didn't and are instead using the proprietary plugin, that could absolutely cause the difference in behavior, as the two codebases have nothing in common.

I just tested ODFE 1.3 vs. Amazon Elasticsearch Service, which is running an older version of the ODFE plugin, and the search results for your query were identical.

[mlrlite]

I was able to use the aws open distro version locally and now both environments are identical. I am still facing the same issue with my original post
query="SELECT * FROM company_1 WHERE name LIKE 'graphic%'" returns me any records with the name like 'graphic' and not starting with. At this point I have to separate each 'phrase' with like statements which seems excessive but the only way people are accomplishing this at the moment.

[aetter]

Hey @mlrlite, are you saying that your local ODFE cluster and your AES domain are now behaving identically, but the behavior is undesirable? Or they behaving differently, and one behavior is desirable and the other isn't?

If they're behaving the same, then it's likely just a SQL bug that we can report and investigate. If they're behaving differently, then there's more troubleshooting to do.

[mlrlite]

Yes my local and aws are now in sync.

Currently I believe we have narrowed the issue down to a SQL bug. Let me provide an example.

With the normal elasticsearch docker install (not ODFE), the following returns me what I want (phrases starting with 'graphic')
query="SELECT * FROM company_1 WHERE name LIKE 'graphic%'"
example results: graphic company 1, graphic llc

With ODFE, the same query returns me any words containing 'graphic'. It just seems the like statement isn't performing the sql like it should.
example results: alpha graphic inc, california graphics corporation

My expectation is that when using 'graphic%' it should perform like any other sql should. In ODFE it does not. what would the next steps be?

[aetter]

Hi @mlrlite, awesome, thanks. That sounds like a bug on our end. I've reported it in the SQL repository here: opendistro-for-elasticsearch/sql#368