0

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?

3
  • please share your code for more information
    – Msquare
    Mar 25 '20 at 11:32
  • 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. Mar 25 '20 at 22:30
  • So please check which value comes Null in your core code.
    – Msquare
    Mar 26 '20 at 3:17
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.