SQL Query with NOT (V 7.2.1)

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL Query with NOT (V 7.2.1)

deniz
I have been trying to get sql queries running, but having trouble while
dealing with the NOT queries.

Basically, the code looks like below


SolrQuery sqlQuery = new SolrQuery();
sqlQuery.setRequestHandler("/sql");
sqlQuery.set("stmt","select collection1.id as collection_1_id,
collection1.email as collection_1_mail from collection1 join collection2 on
collection1.email = collection2.email where collection1.email =
'[hidden email]' limit 2");
System.out.println(solrClient.query("collection2",sqlQuery).getResponse());

Above code is working fine and returning a response like

{result-set={docs=[{collection_1_id=2, collection_1_mail=[hidden email]},
{collection_1_id=1, collection_1_mail=[hidden email]}, {EOF=true,
RESPONSE_TIME=1959}]}}

Then when I try to use a NOT clause as described on
https://lucene.apache.org/solr/guide/7_2/parallel-sql-interface.html


sqlQuery.set("stmt","select collection1.id as collection_1_id,
collection1.email as collection_1_mail from collection1 join collection2 on
collection1.email = collection2.email where NOT (collection1.email =
'[hidden email]') limit 2");

or

sqlQuery.set("stmt","select collection1.id as collection_1_id,
collection1.email as collection_1_mail from collection1 join collection2 on
collection1.email = collection2.email where collection1.email <>
'[hidden email]' limit 2");

I am still getting

{result-set={docs=[{collection_1_id=2, collection_1_mail=[hidden email]},
{collection_1_id=1, collection_1_mail=[hidden email]}, {EOF=true,
RESPONSE_TIME=1959}]}}

as response.

What is more, although the wiki page states '!=' is a valid operator, I am
getting


parse failed: Bang equal '!=' is not allowed under the current SQL
conformance level

in the response.


Is there anything I am missing for using sql queries via SolrJ?



-----
Zeki ama calismiyor... Calissa yapar...
--
Sent from: http://lucene.472066.n3.nabble.com/Solr-User-f472068.html
Zeki ama calismiyor... Calissa yapar...
Reply | Threaded
Open this post in threaded view
|

Re: SQL Query with NOT (V 7.2.1)

deniz
with curl the result is  the same:

curl --data-urlencode "stmt=select id, name from collection where NOT (name
= 'defaultmail')" '<a href="http://server:port/solr/collection/sql'">http://server:port/solr/collection/sql' 

then the response is

.
.
.
{
        "id":113,
        "name":"defaultmail"}
      ,{
        "id":109,
        "name":"defaultmail"}
      ,{
        "EOF":true,
        "RESPONSE_TIME":197}]}}




-----
Zeki ama calismiyor... Calissa yapar...
--
Sent from: http://lucene.472066.n3.nabble.com/Solr-User-f472068.html
Zeki ama calismiyor... Calissa yapar...
Reply | Threaded
Open this post in threaded view
|

Re: SQL Query with NOT (V 7.2.1)

deniz
In reply to this post by deniz
using integers in where clause with NOT is the same, though for that one
using <> as workaround does the job



-----
Zeki ama calismiyor... Calissa yapar...
--
Sent from: http://lucene.472066.n3.nabble.com/Solr-User-f472068.html
Zeki ama calismiyor... Calissa yapar...
Reply | Threaded
Open this post in threaded view
|

Re: SQL Query with NOT (V 7.2.1)

deniz
okay, found a work around for string fields for NOT queries

This query does not filters for NOT:

curl --data-urlencode "stmt=select id, name from collection where NOT (name
= 'defaultmail')" '<a href="http://server:port/solr/collection/sql'">http://server:port/solr/collection/sql'

but after adding sth trivial i.e id > 0 o the where clause as

curl --data-urlencode "stmt=select id, name from collection where id > 0 AND
NOT (name
= 'defaultmail')" '<a href="http://server:port/solr/collection/sql'">http://server:port/solr/collection/sql'

i am not seeing any name field with 'defaultmail' in the response.

I am not sure if this is a bug or just a wrong syntax while using NOT in the
where clause as a single criteria though






-----
Zeki ama calismiyor... Calissa yapar...
--
Sent from: http://lucene.472066.n3.nabble.com/Solr-User-f472068.html
Zeki ama calismiyor... Calissa yapar...
Reply | Threaded
Open this post in threaded view
|

Re: SQL Query with NOT (V 7.2.1)

Joel Bernstein
I believe the NOT should be working. But the joins won't work correctly.
Apache Calcite, which is parsing the query, is attempting to do the joins
but there are no test cases with joins at all. The official docs should say
that joins are not supported.


Joel Bernstein
http://joelsolr.blogspot.com/


On Tue, Oct 16, 2018 at 4:09 AM deniz <[hidden email]> wrote:

> okay, found a work around for string fields for NOT queries
>
> This query does not filters for NOT:
>
> curl --data-urlencode "stmt=select id, name from collection where NOT (name
> = 'defaultmail')" '<a href="http://server:port/solr/collection/sql'">http://server:port/solr/collection/sql'
>
> but after adding sth trivial i.e id > 0 o the where clause as
>
> curl --data-urlencode "stmt=select id, name from collection where id > 0
> AND
> NOT (name
> = 'defaultmail')" '<a href="http://server:port/solr/collection/sql'">http://server:port/solr/collection/sql'
>
> i am not seeing any name field with 'defaultmail' in the response.
>
> I am not sure if this is a bug or just a wrong syntax while using NOT in
> the
> where clause as a single criteria though
>
>
>
>
>
>
> -----
> Zeki ama calismiyor... Calissa yapar...
> --
> Sent from: http://lucene.472066.n3.nabble.com/Solr-User-f472068.html
>