V210 V222 Advance search FATAL MySQL error 1055 field not in GROUP BY

ZC v222 ZC v210
PHP 8.4.16; MySQL 11.8.5-MariaDB-ubu2404; Template: Bootstrap4 3.7.10 (all templates)

Page: Advanced Search (Search)
URL /search_result?keyword=Steering%20head%20bearings&search_in_description=1&categories_id=156&inc_subcat=1&pto=200,
Logfile error (and displayed on screen) is in code block below.

Just place any value in the "Price To" field.

Problem comes from includes/classes/class.search.php.
A quick bodge around line 246 to store the select fields

$select_new_str =  $select_column_list .
            " p.products_sort_order, m.manufacturers_id, p.products_id, pd.products_name,
            p.products_price, p.products_tax_class_id, p.products_price_sorter,
            p.products_qty_box_status, p.master_categories_id, p.product_is_call ";

and a similar one around line 410 to replace $where_str .= " GROUP BY p.products_id, tr.tax_priority"; with ```
$where_str .= " GROUP BY " . $select_new_str . ", p.products_id, tr.tax_priority";

returns the correct results.
I have not tested it to see what else i tmay have broken

Logfile```
[21-May-2026 18:13:11 Australia/Brisbane] Request URI: /search_result?keyword=Steering%20head%20bearings&search_in_description=1&categories_id=156&inc_subcat=1&pto=200, IP address: 1.147.108.201, Language id 1
#0 [internal function]: zen_debug_error_handler()
#1 /includes/classes/db/mysql/query_factory.php(757): trigger_error()
#2 /includes/classes/db/mysql/query_factory.php(701): queryFactory->show_error()
#3 /includes/classes/db/mysql/query_factory.php(314): queryFactory->set_error()
#4 /includes/modules/xx_bootstrap_xx/product_listing.php(137): queryFactory->Execute()
#5 /includes/templates/xx_bootstrap_xx/templates/tpl_modules_product_listing.php(12): require('...')
#6 /includes/templates/xx_bootstrap_xx/templates/tpl_search_result_default.php(71): require('...')
#7 /includes/templates/xx_bootstrap_xx/common/tpl_main_page.php(233): require('...')
#8 /index.php(109): require('...')
--> PHP Fatal error: FATAL MySQL error 1055: 'zcXXX_prod.p.products_model' isn't in GROUP BY :: SELECT DISTINCT p.products_model, p.products_image, p.products_quantity, p.products_sort_order, m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_price_sorter, p.products_qty_box_status, p.master_categories_id, p.product_is_call , SUM(tr.tax_rate) AS tax_rate FROM (products p LEFT JOIN manufacturers m USING(manufacturers_id), products_description pd, categories c, products_to_categories p2c ) LEFT JOIN meta_tags_products_description mtpd ON (mtpd.products_id= p2c.products_id AND mtpd.language_id = 1) LEFT JOIN tax_rates tr ON p.products_tax_class_id = tr.tax_class_id LEFT JOIN zones_to_geo_zones gz ON tr.tax_zone_id = gz.geo_zone_id AND (gz.zone_country_id IS null OR gz.zone_country_id = 0 OR gz.zone_country_id = 13) AND (gz.zone_id IS null OR gz.zone_id = 0 OR gz.zone_id = 185) WHERE (p.products_status = 1 AND p.products_id = pd.products_id AND pd.language_id = 1 AND p.products_id = p2c.products_id AND p2c.categories_id = c.categories_id AND p2c.products_id = p.products_id AND p2c.products_id = pd.products_id AND (p2c.categories_id = 156) AND ( ( pd.products_name LIKE '%steering%' OR p.products_model LIKE '%steering%' OR m.manufacturers_name LIKE '%steering%' OR mtpd.metatags_keywords LIKE '%steering%' OR mtpd.metatags_description LIKE '%steering%' OR pd.products_description LIKE '%steering%') AND ( pd.products_name LIKE '%head%' OR p.products_model LIKE '%head%' OR m.manufacturers_name LIKE '%head%' OR mtpd.metatags_keywords LIKE '%head%' OR mtpd.metatags_description LIKE '%head%' OR pd.products_description LIKE '%head%') AND ( pd.products_name LIKE '%bearings%' OR p.products_model LIKE '%bearings%' OR m.manufacturers_name LIKE '%bearings%' OR mtpd.metatags_keywords LIKE '%bearings%' OR mtpd.metatags_description LIKE '%bearings%' OR pd.products_description LIKE '%bearings%') )) AND (p.products_price_sorter * IF(gz.geo_zone_id IS NULL, 1, 1 + (tr.tax_rate / 100)) <= 200) GROUP BY p.products_id, tr.tax_priority ORDER BY p.products_sort_order, pd.products_name LIMIT 8 ==> (as called by) /includes/modules/xx_bootstrap_xx/product_listing.php on line 137 <== in /includes/classes/db/mysql/query_factory.php on line 757.

This only occurs when DB_MYSQL_MODE is set to ONLY_FULL_GROUP_BY.
I had though that ZC since 2.0.0 would handle this as "newer MySQL versions (5.7 and 8.0+) enable ONLY_FULL_GROUP_BY by default"

I was not able to reproduce this with ZC v2.2.2 (released) or ZC v3.0.0 (dev) on PHP 8.4.21 and MySQL 8.4.9 with ONLY_FULL_GROUP_BY set.
File includes/classes/class.search.php does not have any variable called $select_new_str. Here is what I have on line 242:

        $select_str = "SELECT DISTINCT " . $select_column_list .
            " p.products_sort_order, m.manufacturers_id, p.products_id, pd.products_name,
            p.products_price, p.products_tax_class_id, p.products_price_sorter,
            p.products_qty_box_status, p.master_categories_id, p.product_is_call ";

You must have a modified version of ZC.

I *was *able to reproduce this on zc222 with database engine MySQL 10.4.32-MariaDB and ONLY_FULL_GROUP_BY.

The error received indicated that 'zc220.p.products_image' isn't in GROUP BY. However, the GROUP BY clause contains GROUP BY p.products_id, tr.tax_priority and the tr.tax_priority field isn't in the overall SELECT clause.

I'll note that the search code has included that GROUP BY clause (only when DISPLAY_PRICES_WITH_TAX is on) since as far back as zc157 (albeit not in a class) and I don't see its purpose.

GitHub issue for tracking: https://github.com/zencart/zencart/issues/7741

I am still not able to reproduce it. I did some new tests with ZC v3.0.0 on PHP 8.5.6 and MariaDB 11.8.6 and both ONLY_FULL_GROUP_BY and DISPLAY_PRICES_WITH_TAX are activated (verified). It seems the sorting is done in results page by the 'Filter by' and 'Sort by' fields.

pilou2:

I am still not able to reproduce it. I did some new tests with ZC v3.0.0 on PHP 8.5.6 and MariaDB 11.8.6 and both ONLY_FULL_GROUP_BY and DISPLAY_PRICES_WITH_TAX are activated (verified). It seems the sorting is done in results page by the 'Filter by' and 'Sort by' fields.
With any zc version from 210 to 300 set "define('DB_MYSQL_MODE', 'ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION');" in your configure.php and then go to the advanced search page and enter a value in the "Price To" field.

The "$select_new_str" code was my quick fix to get around the issue without needing to examine the code further.

OldNGrey:

With any zc version from 210 to 300 set "define('DB_MYSQL_MODE', 'ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION');" in your configure.php and then go to the advanced search page and enter a value in the "Price To" field.

The "$select_new_str" code was my quick fix to get around the issue without needing to examine the code further.

I tried it in case something was overriding the MySQL server settings and it did not change a thing. No error and no log. As I said before, the sql mode has been verified and contains ONLY_FULL_GROUP_BY. It is actually set to this: ```
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Results display as they should depending on the max price I set in the form. I tried to limit to some category or add some keywords and never had a problem...
There must be something else that is different.

I captured the actual SQL query when I set max price at 20 with demo data:

SELECT DISTINCT p.products_image, p.products_quantity, p.products_weight, m.manufacturers_name, p.products_model, p.products_sort_order, m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_price_sorter, p.products_qty_box_status, p.master_categories_id, p.product_is_call , SUM(tr.tax_rate) AS tax_rate FROM (products p LEFT JOIN manufacturers m USING(manufacturers_id), products_description pd, categories c, products_to_categories p2c ) LEFT JOIN meta_tags_products_description mtpd ON (mtpd.products_id= p2c.products_id AND mtpd.language_id = 1) LEFT JOIN tax_rates tr ON p.products_tax_class_id = tr.tax_class_id LEFT JOIN zones_to_geo_zones gz ON tr.tax_zone_id = gz.geo_zone_id AND (gz.zone_country_id IS null OR gz.zone_country_id = 0 OR gz.zone_country_id = 223) AND (gz.zone_id IS null OR gz.zone_id = 0 OR gz.zone_id = 18) WHERE (p.products_status = 1 AND p.products_id = pd.products_id AND pd.language_id = 1 AND p.products_id = p2c.products_id AND p2c.categories_id = c.categories_id ) AND (p.products_price_sorter * IF(gz.geo_zone_id IS NULL, 1, 1 + (tr.tax_rate / 100)) <= 20) GROUP BY p.products_id, tr.tax_priority ORDER BY p.products_sort_order, pd.products_name

It seems that p.products_id being a primary key and because of all the relations between table, all column in SELECT clause can be determined uniquely which avoid the SQL error 1055. If I change to another column in the GROUP BY clause I get the error 1055.
There are explanation on exception case for ONLY_FULL_GROUP_BY at that page. It is not easy to grasp...
It would be intersting to see one of your SQL queries when you get the error 1055.
To display it on top of result page, I just add this "var_dump($listing_sql);" after line 46 ($listing_sql = $search->buildSearchSQL();) of file includes/modules/pages/search_result/header_php.php.

I believe that I've got this corrected; see https://github.com/zencart/zencart/pull/7761 for the changes.

Please let me know (either here or on the GitHub PR) if I'm incorrect!

The function zen_config is not available in zc2.2.2. It looks like it was introduced in 3.0.0.

The fix works in zc3.0.0.

For me, the only way to trigger SQL error 1055 was by modifying the query directly in the code... With the PR, nothing changed. But the side effect is that now, when multiple taxes are applied, even compound, the price range displayed seems to be correct. Before, as soon as there was more than one tax, the price range of products in search results was shifted, not accurate at all.

OldNGrey:

The function zen_config is not available in zc2.2.2. It looks like it was introduced in 3.0.0.

The fix works in zc3.0.0.
True, on the zen_config function; thanks for the update!

pilou2:

For me, the only way to trigger SQL error 1055 was by modifying the query directly in the code... With the PR, nothing changed. But the side effect is that now, when multiple taxes are applied, even compound, the price range displayed seems to be correct. Before, as soon as there was more than one tax, the price range of products in search results was shifted, not accurate at all.

Yes, I'd noticed when testing that the previous sum of taxes was "off", possibly the sum of all taxes?