I also agree that we should preserve SQL semantics, regardless of the adapter. For more details see a good article about Three Valued Logic <https://modern-sql.com/concept/three-valued-logic>.
On Tue, Oct 5, 2021 at 11:32 AM Julian Hyde <[email protected]> wrote: > 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. > >> >
