Datastax - Cassandra CQL Solr query pagination issue

Question

I was trying to implement pagination using solr query, but not working properly from CQL solr. Please advise me, i am new to Cassandra and solr.

I am using cqlsh 5.0.1 | Cassandra 3.0.10.1443 | DSE 5.0.4 | CQL spec 3.4.0

1) select country_id,country_name from rptavlview.country where solr_query='{"q":":","start":"1"}' limit 5;

when i tried with the above query, i am getting error as below

InvalidRequest: Error from server: code=2200 [Invalid query] message="You have driver paging active which also activates Solr deep pagination. The 'start' parameter is not allowed. Please either deactivate paging or read about Solr deep paging restrictions and fix accordingly."

2) select country_id,country_name from rptavlview.country where solr_query='{"q":":","start":"1","rows":"5"}';

InvalidRequest: Error from server: code=2200 [Invalid query] message="Unsupported query parameter: rows"

When i tried from solr it worked

http://MYIPADDRESS:8983/solr/rptavlview.country/select?q=%3A&start=1&rows=5&wt=json&indent=true

"response": { "numFound": 237, "start": 1, "docs": [ { "_uniqueKey": "[\"49\",\"Christmas Island\"]",
"country_name": "Christmas Island",
"country_name_ar": "جزيرة كريسماس ",
"country_id": "49" }, { "_uniqueKey": "[\"51\",\"Colombia\"]",
"country_name": "Colombia",
"country_name_ar": "كولمبيا",
"country_id": "51" }, { "_uniqueKey": "[\"56\",\"Cuba\"]",
"country_name": "Cuba",
"country_name_ar": "كوبا",
"country_id": "56" }, { "_uniqueKey": "[\"57\",\"Cyprus\"]",
"country_name": "Cyprus",
"country_name_ar": "قبرص ",
"country_id": "57" }, { "_uniqueKey": "[\"59\",\"Democratic Republic of the Congo\"]",
"country_name": "Democratic Republic of the Congo",
"country_name_ar": "جمهورية الكونغو الديمقراطية",
"country_id": "59" } ] } }

why pagination is not working with my CQL solr query ?

Answer 1

Solr supports two different ways of doing pagination - one is the old start parameter which tells Solr at which element to start the result set, and the new one is cursorMark - which tells Solr where the last result set ended and to start presenting results from the mark and onwards.

The latter way (also referenced as "deep pagination") is the way supported by Cassandra with its paging_state element, so my guess is that the Solr integration will do its own pagination based on the properties given in the CQL query, and not in the JSON used as a Solr query (as it would have overwritten the properties of the query).

I've described the difference between using a paging state / cursor mark and "old style" pagination in a different answer, which should show why using the state / mark is superior in a distributed context.

Answer 2

Few basic points:

1. CQL Solr queries are defaulted to an equivalent LIMIT 10.
2. Pagination is not on by default.
3. SELECT select expression FROM table [WHERE solr_query = 'search expression'] [LIMIT n]

To answer your first question: "Error from server: code=2200 [Invalid query] message="You have driver paging active which also activates Solr deep pagination. The 'start' parameter is not allowed. Please either deactivate paging or read about Solr deep paging restrictions and fix accordingly"

==> Please check "cql_solr_query_paging" property. File name : DSE_install_location/resources/dse/conf/dse.yaml

Valid values are : 'driver' and  'off';

"When 'driver', DSE Search will use pagination (aka cursors) when it detects the driver is using pagination" "When 'off' it will ignore the driver's setting and not use pagination. In this case the query parameter 'paging' can override it." By default its 'off'. So, in your case current value is 'driver'.

So, you should modify your query to :

select country_id,country_name from rptavlview.country where solr_query='{"q":"*:*"}' limit 5; 

OR:

select country_id,country_name from rptavlview.country where solr_query='{"q":"*:*","paging": null,"start":1}' limit 5;

With Turn off the pagination property and restart dse.

"InvalidRequest: Error from server: code=2200 [Invalid query] message="Unsupported query parameter: rows" ==> Use the LIMIT clause to specify how many rows to return.

More information (please read): https://docs.datastax.com/en/datastax_enterprise/4.8/datastax_enterprise/srch/srchCql.html

Let me know if you need more information.

Answer 3

answer for my posted question can be resolved by

In CQL

cqlsh> paging off;

cqlsh> select country_id,country_name from rptavlview.country where solr_query='{"q":":","start":1}' limit 5;

In Java - set Fetch Size for statement

Statement statement = new SimpleStatement(cql_query_string); statement.setFetchSize(Integer.MAX_VALUE);