SQL error on changing the page number to large number

Question

I have a strange problem. Whenever I change the page number to some none existing page number then the system throws sql error.

For example http://domain.com/category.html?p=1 works as expected but when I change it to http://domain.com/category.html?p=100 then it throws below error. In that category only 1-5 pages available.

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') ASC' at line 4, query was: SELECT e.*, price_index.price, price_index.tax_class_id, price_index.final_price, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS minimal_price, price_index.min_price, price_index.max_price, price_index.tier_price, IFNULL(review_summary.reviews_count, 0) AS reviews_count, IFNULL(review_summary.rating_summary, 0) AS rating_summary, stock_status_index.stock_status AS is_salable FROM catalog_product_entity AS e INNER JOIN catalog_product_index_price AS price_index ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 LEFT JOIN review_entity_summary AS review_summary ON e.entity_id = review_summary.entity_pk_value AND review_summary.store_id = 1 AND review_summary.entity_type = (SELECT review_entity.entity_id FROM review_entity WHERE (entity_code = 'product')) INNER JOIN cataloginventory_stock_status AS stock_status_index ON e.entity_id = stock_status_index.product_id WHERE ((stock_status_index.stock_status = 1) AND (e.entity_id IN (NULL))) AND (e.created_in <= 1) AND (e.updated_in> 1) ORDER BY FIELD(e.entity_id,) ASC

As you can see some SQL is missing after FIELD(e.entity_id,. This is how it should be:

SELECT e.*, price_index.price, price_index.tax_class_id, price_index.final_price, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS minimal_price, price_index.min_price, price_index.max_price, price_index.tier_price, IFNULL(review_summary.reviews_count, 0) AS reviews_count, IFNULL(review_summary.rating_summary, 0) AS rating_summary, stock_status_index.stock_status AS is_salable FROM catalog_product_entity AS e INNER JOIN catalog_product_index_price AS price_index ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 LEFT JOIN review_entity_summary AS review_summary ON e.entity_id = review_summary.entity_pk_value AND review_summary.store_id = 1 AND review_summary.entity_type = (SELECT review_entity.entity_id FROM review_entity WHERE (entity_code = 'product')) INNER JOIN cataloginventory_stock_status AS stock_status_index ON e.entity_id = stock_status_index.product_id WHERE ((stock_status_index.stock_status = 1) AND (e.entity_id IN (NULL))) AND (e.created_in <= 1) AND (e.updated_in> 1) ORDER BY FIELD(e.entity_id,NULL) ASC

It looks NULL is missing.

Diff image: enter image description here

Magento 2.3.3 with elasticsearch 6+

Any idea why this happening? Which section should I be checking to fix this?

• Msquare: please share your code for more information
• Adarsh Khatri: It's a Magento core code. I haven't made any changes. And I confirm it is doing this only when using elasticsearch. With mysql search, it's working fine.
• Msquare: So please check which value comes Null in your core code.

Answer 1

try this way to get collection

$page = ($this->getRequest()->getParam('p')) ? $this->getRequest()->getParam('p') : 1; \\ default value 
$pageSize = ($this->getRequest()->getParam('limit')) ? $this->getRequest()->getParam('limit') : 5; \\ default value 
$collection = .................................. \\ get your collection here 
$collection->setPageSize($pageSize);
$collection->setCurPage($page);
return $collection;

And if you filter dynamic value must check value not NULL