I support Stamatis’ position. Our SQL adapter must implement SQL semantics. That is what users of a SQL interface want and expect.
If Elasticsearch’s data model has nuances that cannot be captured in SQL, feel free to add extra operators. If name is a multi-valued attribute, then some ideas are IS_EMPTY(name), VALUE_COUNT(name), VALUE_SET(name). I’ve added these comments to https://issues.apache.org/jira/browse/CALCITE-4292. > On Oct 5, 2021, at 2:57 AM, Stamatis Zampetakis <[email protected]> wrote: > > Hi all, > > The question is basically how the following SQL statement should behave for > rows where the name is NULL in the ElasticSearch adapter. > > SELECT * from zips WHERE name <> "NMAX" > > I did add my comments in the JIRA case but it would be good if somebody > also expresses an opinion so that we resolve/close the issue. > > Best, > Stamatis > >> On Tue, Sep 28, 2021 at 2:52 PM Shlok Srivastava >> <[email protected]> wrote: >> >> Hi Community, >> >> Issue id -CALCITE-4292 >> Issue name -Wrong results in ElasticSearch when query contains NOT EQUAL >> >> We made a change to modify not equals criteria in elasticsearch adapters >> as it was not in sync with Elasticsearch behavior. >> >> In-case of Not_Equals condition, the default behaviour of ElasticSearch as >> well as JSON path is to select records in which the mentioned field is >> missing, but as calcite prefers SQL semantics it adds EXISTS condition as >> well. >> Adding additional EXISTS condition in NOT_EQUALS criteria deviates from >> ElasticSearch behaviour. As the adapter is for ElasticSearch it should >> support ES behaviour. If someone requires exists along with NO_EQUALS >> condition it can be explicitly added in where condition but it can't be >> removed unless the code is fixed. >> >> So, this defect should be fixed to support default ElasticSearch behavior. >> >> For this change we have been informed that we require community feedback, >> so please share your thoughts/approval for same. >> >> Thanks, >> Shlok >> >> ________________________________ >> >> >> >> >> >> >> NOTE: This message may contain information that is confidential, >> proprietary, privileged or otherwise protected by law. The message is >> intended solely for the named addressee. If received in error, please >> destroy and notify the sender. Any use of this email is prohibited when >> received in error. Impetus does not represent, warrant and/or guarantee, >> that the integrity of this communication has been maintained nor that the >> communication is free of errors, virus, interception or interference. >>
