Table/Index name alias is breaking where clause

[Nitinrajyadav]

localhost:9200/_opendistro/_sql/_explain

Query
{ "query": "select slatwallpr0_.activeFlag, slatwallpr0_.calculatedQATS, slatwallpr0_.calculatedTitle, slatwallpr0_.deferredRevenueFlag, slatwallpr0_.productCode, slatwallpr0_.productID from testingagainn slatwallpr0_ where slatwallpr0_.calculatedQATS >500 or slatwallpr0_.calculatedTitle like '%pix%' or slatwallpr0_.productCode like '%pix%' or slatwallpr0_.productID like '%pix%'" }

Response
{"from":0,"size":200,"query":{"bool":{"filter":[{"bool":{"must":[{"bool":{"should":[{"range":{"slatwallpr0_.calculatedQATS":{"from":500,"to":null,"include_lower":false,"include_upper":true,"boost":1.0}}},{"wildcard":{"slatwallpr0_.calculatedTitle":{"wildcard":"*pix*","boost":1.0}}},{"wildcard":{"slatwallpr0_.productCode":{"wildcard":"*pix*","boost":1.0}}},{"wildcard":{"slatwallpr0_.productID":{"wildcard":"*pix*","boost":1.0}}}],"adjust_pure_negative":true,"boost":1.0}}],"adjust_pure_negative":true,"boost":1.0}}],"adjust_pure_negative":true,"boost":1.0}},"_source":{"includes":["activeFlag","calculatedQATS","calculatedTitle","deferredRevenueFlag","productCode","productID"],"excludes":[]}}

see slatwallpr0_.calculatedTitle, slatwallpr0_.productCode

[abbashus]

Thanks @Nitinrajyadav , can you briefly describe the issue.
What version of plugin are you using?
Can you also share the mappings of testingagainn index?

[Nitinrajyadav]

@abbashus I'm using open distro 0.9.0
Mappings

 "testingagainn": {
        "aliases": {},
        "mappings": {
            "_doc": {
                "properties": {
                    "activeFlag": {
                        "type": "boolean",
                        "fields": {
                            "raw": {
                                "type": "keyword"
                            }
                        }
                    },
                    "calculatedQATS": {
                        "type": "integer",
                        "fields": {
                            "raw": {
                                "type": "keyword"
                            }
                        }
                    },
                    "calculatedTitle": {
                        "type": "text",
                        "term_vector": "with_positions",
                        "fields": {
                            "raw": {
                                "type": "keyword"
                            }
                        }
                    },
                    "deferredRevenueFlag": {
                        "type": "boolean",
                        "fields": {
                            "raw": {
                                "type": "keyword"
                            }
                        }
                    },
                    "productCode": {
                        "type": "text",
                        "term_vector": "with_positions",
                        "fields": {
                            "raw": {
                                "type": "keyword"
                            }
                        }
                    },
                    "productID": {
                        "type": "text",
                        "term_vector": "with_positions",
                        "fields": {
                            "raw": {
                                "type": "keyword"
                            }
                        }
                    }
                }
            }
        },
        "settings": {
            "index": {
                "creation_date": "1561661937266",
                "number_of_shards": "5",
                "number_of_replicas": "1",
                "uuid": "aa5RGC6WSC2o5_eWOPn7Mg",
                "version": {
                    "created": "6070199"
                },
                "provided_name": "testingagainn"
            }
        }
    }
}

[Nitinrajyadav]

@abbashus Having the same problem in OpenDistro V-1.0.0.

To explain the problem "When I'm using Index-name alias, the plugin understands the alias.columnName for the SELECT statement it's removing alias and only including columnName (have a look at the response from /_explain API), but for WHERE clause it's treating the alias.columnName as columnName (again have a look at response from /_explain API).

It works, if I change the WHERE clause from WHERE alias.columnName condition to WHERE colunmName condition(notice the missing alias) and leave the select statement as is(Using table-name alias).
I'm getting the expected results for this query.
"select slatwallpr0_.activeFlag, slatwallpr0_.calculatedQATS, slatwallpr0_.calculatedTitle, slatwallpr0_.deferredRevenueFlag, slatwallpr0_.productCode, slatwallpr0_.productID from testingagainn slatwallpr0_ where calculatedQATS >500 or calculatedTitle like '%pix%' or productCode like '%pix%' or productID like '%pix%'".

And to my understanding, the issue looks like it's related to distinguishing b/w
nested-field-name (columnName.nestedPropertyName)
and alias.columnName for WHERE clause.

[abbashus]

Thanks @Nitinrajyadav for providing details, we will investigate this issue and update the thread.

[dai-chen]

This seems a critical issue. Query as simple as below returns incorrect empty response.

POST _opendistro/_sql
{
  "query": "SELECT a.age FROM accounts a WHERE a.age > 0"
}