In nested, operator <> interpreted as LessThanOrGreater, should there be a difference with NotEqual?

[FreCap]

Creating a test I noticed that in nested the symbol <> is confusing since it is LessThanOrGreater while != is NotEqual:

    @Test
    public void testDiff() {
        try {

        same(
                query("SELECT region FROM team WHERE nested(employees.age, 'employees' <> 26)"),
                query("SELECT region FROM team WHERE nested(employees.age, 'employees'!= 26 ) ")
        );

        }catch (Throwable e){
            System.out.print(e);
        }
    }

parameters = {ArrayList@2191}  size = 2
 0 = {SQLIdentifierExpr@2194} "employees.age"
 1 = {SQLBinaryOpExpr@2195} "'employees' <> 26"
  left = {SQLCharExpr@2306} "'employees'"
  right = {SQLIntegerExpr@2307} "26"
  operator = {SQLBinaryOperator@2308} "LessThanOrGreater"
  dbType = null
  parent = {SQLMethodInvokeExpr@2186} "nested(employees.age, com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr@2d3a5841)"
  attributes = null
parent = {MySqlSelectQueryBlock@2172} "com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock@c7242101"

parameters = {ArrayList@2292}  size = 2
 0 = {SQLIdentifierExpr@2295} "employees.age"
 1 = {SQLBinaryOpExpr@2296} "'employees' != 26"
  left = {SQLCharExpr@2299} "'employees'"
  right = {SQLIntegerExpr@2300} "26"
  operator = {SQLBinaryOperator@2301} "NotEqual"
  dbType = null
  parent = {SQLMethodInvokeExpr@2180} "nested(employees.age, com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr@2def3ae7)"
  attributes = null
parent = {MySqlSelectQueryBlock@2175} "com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock@c7242101"
attributes = null

[dai-chen]

Thanks for reporting the issue! Will investigate.

[FreCap]

In several points in code only <> is defined which might be the cause I get:

{
  "es_error_json": {
    "details": "Unsupported operation: !=",
    "reason": "Invalid SQL query",
    "type": "SqlParseException"
  },

Like in here:

• sql/legacy/src/main/java/com/amazon/opendistroforelasticsearch/sql/legacy/domain/Condition.java

  Line 98 in 2aeb80b

  operStringToOpear.put("<>", N);

• sql/legacy/src/main/java/com/amazon/opendistroforelasticsearch/sql/legacy/query/maker/Maker.java

  Line 424 in 2aeb80b

  case "=":

I'm still wrapping my head around on the project and its code path before contributing actively. I see a lo of code under "legacy", is there some documentation on what is the faith of that code (being replaced/deprecated)?

[dai-chen]

@FreCap Yeah, basically we want to deprecate legacy engine gradually. We encourage you to play with our new SQL query engine. For more details, recently I updated few docs including README and Dev Guide in this PR: #820. More design docs regarding this improved query engine will be uploaded later. Thanks!

[FreCap]

Thanks @dai-chen, just wondering, will nested() keyword be supported in the new engine? PS: the links in the PR are referring to your private space.

[dai-chen]

@FreCap Sorry, just realized that the branch was merged and deleted. You can check out: https://github.com/opendistro-for-elasticsearch/sql#experimental and https://github.com/opendistro-for-elasticsearch/sql/blob/develop/docs/developing.rst. Yeah, nested field query is an important feature. Previously, there are quite a few issues for this support in legacy. We're evaluating and will add much better support to the new engine too. Thanks!