You are viewing a plain text version of this content. The canonical link for it is here.
Posted to solr-user@lucene.apache.org by deniz <de...@gmail.com> on 2018/10/15 08:52:09 UTC
SQL Query with NOT (V 7.2.1)
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 =
'some@email.com' 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=some@email.com},
{collection_1_id=1, collection_1_mail=some@email.com}, {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 =
'some@email.com') 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 <>
'some@email.com' limit 2");
I am still getting
{result-set={docs=[{collection_1_id=2, collection_1_mail=some@email.com},
{collection_1_id=1, collection_1_mail=some@email.com}, {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
Re: SQL Query with NOT (V 7.2.1)
Posted by deniz <de...@gmail.com>.
with curl the result is the same:
curl --data-urlencode "stmt=select id, name from collection where NOT (name
= 'defaultmail')" '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
Re: SQL Query with NOT (V 7.2.1)
Posted by Joel Bernstein <jo...@gmail.com>.
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 <de...@gmail.com> 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')" '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')" '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
>
Re: SQL Query with NOT (V 7.2.1)
Posted by deniz <de...@gmail.com>.
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')" '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')" '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
Re: SQL Query with NOT (V 7.2.1)
Posted by deniz <de...@gmail.com>.
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