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

FreCap commented 3 years ago

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 commented 3 years ago

Thanks for reporting the issue! Will investigate.

FreCap commented 3 years ago

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:

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 commented 3 years ago

@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: https://github.com/opendistro-for-elasticsearch/sql/pull/820. More design docs regarding this improved query engine will be uploaded later. Thanks!

FreCap commented 3 years ago

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 commented 3 years ago

@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!

opendistro-for-elasticsearch / sql

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