Product Purchases SubPanel not populated

[vmijalkovic]

Issue

The product is listed as a line item in the quote and quote's stage is set to Closed Accepted.
When trying to collapse Purchases SubPanel on the product details view nothing to see there.

Following error is logged:
[FATAL] Error running count query for AOS_Products List: Query Failed:(
SELECT count() c FROM (
SELECT
aos_quotes.,
accounts.id AS account_id,
accounts.name AS billing_account,

				opportunity_id AS opportunity,
				billing_contact_id AS billing_contact,
				'' AS created_by_name,
				'' AS modified_by_name,
				'' AS assigned_user_name
			FROM
				aos_products

			JOIN aos_products_quotes ON aos_products_quotes.product_id = aos_products.id AND aos_products.id = N'c8fe489f-5fa3-9a01-d541-5acf52340755' AND aos_products_quotes.deleted = 0 AND aos_products.deleted = 0
			JOIN aos_quotes ON aos_quotes.id = aos_products_quotes.parent_id AND aos_quotes.stage = N'Closed Accepted' AND aos_quotes.deleted = 0
			JOIN accounts ON accounts.id = aos_quotes.billing_account_id -- AND accounts.deleted = 0

			GROUP BY accounts.id
		) AS aos_quotes

	)::: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Column 'aos_quotes.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Possible Fix

I have commented out grouping clause:
--GROUP BY accounts.id
I don't vote for a fix like this but it surely made my subpanel populated.
It would be better to have grouping but solution like this one #2869 are simply not good practice and do not work for MSSQL.

Context

This bug is actually a very nasty one. Imagine sales guys willing to easily identify when a product has been sold last time and for what amount. Yes they may run some report but Purchases subpanel is made for this and it is not working.

Your Environment

• SuiteCRM Version 7.10.1
• Any browser
• MSSQL 2016, PHP 7.1
• Windows server 2012 R2

[pgorod]

I came across what appears to be a different manifestation of the same problem. I get the same SQL error.

But my steps to reproduce are simpler: just go in Product List view and click a product, and I get the error, and the detail view doesn't show. I am on 7.10.4, Ubuntu, MySQL.

I couldn't reproduce this on the live demo with 7.10.4, it works well there...

Removing the "group by" clause also fixes it for me: https://github.com/salesagility/SuiteCRM/blame/master/modules/AOS_Products/AOS_Products.php#L103

But this code hasn't been changed in ages, I wonder what's really going on here...

[pgorod]

The problem is related to AJAX: I can enter the Product's detail view fine, if coming from a non-ajax link (for example, from a line item in a quote, referencing that product).

If I am coming from an ajax link (products list view, or recently viewed link) I get the error.