You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Shlok Srivastava <sh...@impetus.com.INVALID> on 2021/09/28 12:51:22 UTC
(CALCITE-4292) Wrong results in ElasticSearch when query contains NOT
EQUAL
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.
Re: (CALCITE-4292) Wrong results in ElasticSearch when query contains
NOT EQUAL
Posted by Andrei Sereda <an...@sereda.cc>.
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 <jh...@gmail.com> 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 <za...@gmail.com>
> 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
> >> <sh...@impetus.com.invalid> 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.
> >>
>
Re: (CALCITE-4292) Wrong results in ElasticSearch when query contains NOT EQUAL
Posted by Julian Hyde <jh...@gmail.com>.
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 <za...@gmail.com> 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
>> <sh...@impetus.com.invalid> 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.
>>
Re: (CALCITE-4292) Wrong results in ElasticSearch when query contains
NOT EQUAL
Posted by Stamatis Zampetakis <za...@gmail.com>.
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
<sh...@impetus.com.invalid> 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.
>