In admin categories sort category status enable error

[diamond1]

Hello everyone!
In admin categories sort category status enable, then on category sort by weight, product name, product price - and + price gives me error

In admin categories order category status enable, then on category by weight, product name, product price - and + price gives me error WARNING: An Error occurred, please let us know! If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you entered valid data.
[12-Mar-2023 10:46:26 UTC] Request URI: /Quick-NcQ-shalL/index.php?cmd=category_product_listing&cPath=, IP address: ::1, Language id 1
#0 [internal function]: zen_debug_error_handler()
#1 /Users/user/Sites/localhost/zencart-158/includes/classes/db/mysql/query_factory.php(667): trigger_error()
#2 /Users/user/Sites/localhost/zencart-158/includes/classes/db/mysql/query_factory.php(634): queryFactory->show_error()
#3 /Users/user/Sites/localhost/zencart-158/includes/classes/db/mysql/query_factory.php(275): queryFactory->set_error()
#4 /Users/user/Sites/localhost/zencart-158/Quick-NcQ-shalL/includes/classes/split_page_results.php(220): queryFactory->Execute()
#5 /Users/user/Sites/localhost/zencart-158/Quick-NcQ-shalL/includes/classes/split_page_results.php(131): splitPageResults->numberRows()
#6 /Users/user/Sites/localhost/zencart-158/Quick-NcQ-shalL/category_product_listing.php(829): splitPageResults->__construct()
#7 /Users/user/Sites/localhost/zencart-158/Quick-NcQ-shalL/index.php(11): require('/Users/gio...')
--> PHP Fatal error: 3065:Expression #1 of ORDER BY clause is not in SELECT list, references column 'zencart158.p.products_price_sorter' which is not in SELECT list; this is incompatible with DISTINCT :: SELECT count(*) as total FROM (SELECT DISTINCT p.products_type, p.products_id, pd.products_name, p.products_quantity, p.products_price, p.products_status, p.products_model, p.products_sort_order, p.master_categories_id FROM products p LEFT JOIN products_description pd ON (pd.products_id = p.products_id) LEFT JOIN products_to_categories p2c ON (p2c.products_id = p.products_id) WHERE pd.language_id = 1 AND p2c.categories_id=0 ORDER BY p.products_price_sorter, pd.products_name) countresults ==> (as called by) /Users/user/Sites/localhost/zencart-158/Quick-NcQ-shalL/includes/classes/split_page_results.php on line 220 <== in /Users/user/Sites/localhost/zencart-158/includes/classes/db/mysql/query_factory.php on line 667.

Thanks again,
Gio

[swguy]

Yep, this a bug. It will be fixed in 1.5.8a.
If you need a workaround, you can modify admin/includes/configure.php to use

define('DB_MYSQL_MODE','NO_ENGINE_SUBSTITUTION');

[swguy]

PR: https://github.com/zencart/zencart/pull/5643

[lat9]

What version of MySQL is in use? The admin's Tools :: Server/Version Info tool will tell you.

Based on the debug-log you provided (thanks!), I can see that some of the Categories/Product Display Order selections when viewing a list of products in a category specify fields in their ordering that aren't also gathered for the selection:

Products Price+, Products Name ... products_price_sorter is missing
Products Price-, Products Name ... products_price_sorter is missing
Weight .................................... products_weight is missing

[swguy]

Nice catch on the weight. The PR has been updated.

[diamond1]

What to say? you are super fast thank you very much Lat9 Swguy


HTTP Server: Apache/2.4.54 (Unix) mod_fastcgi/mod_fastcgi-SNAP-0910052141 PHP/8.2.0 OpenSSL/1.0.2u mod_wsgi/3.5 Python/2.7.18
Versione PHP: 8.2.0 (Zend: 4.2.0)
PHP File Uploads: On
Upload Max Size: 32M
PHP Memory Limit: 4G
POST Max Size: 8M
Database Engine: MySQL 5.7.39