v5, v7, Chart and group_by issue MSSQL, POSTGRES

[platipusica]

Placeholder for mssql Charts issue:

    var acc = item.task.products.copy({handlers: false});
    acc.open(
        {
            fields: ['id', 'category', 'standard_cost'], 
            funcs: {'standard_cost': 'avg'},
            group_by: ['category'],
            order_by: ['category'],
            limit: 10
        }, 

Traceback:

127.0.0.1 - - [30/Apr/2024 13:17:56] "POST /api HTTP/1.1" 200 -
ERROR - (8120, b"Column 'PRODUCTS.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n")
Traceback (most recent call last):

[platipusica]

Temp solution:

• remove all non aggregate or group by like 'id' from fields.

The problem with this temporary solution is that it is impossible to develop on SQLITE and move the project to MSSQL, without touching the code.

[platipusica]

Investigated further, first SQL is ok:

SELECT b."PRODUCT_ID", b."QUANTITY", PRODUCT_ID_LOOKUP FROM (SELECT "PURCHASE_ORDER_DETAILS"."PRODUCT_ID", SUM("PURCHASE_ORDER_DETAILS"."QUANTITY") AS "QUANTITY", PRODUCTS_231."PRODUCT_NAME" AS PRODUCT_ID_LOOKUP, ROW_NUMBER() OVER ( ORDER BY sum("PURCHASE_ORDER_DETAILS"."QUANTITY") DESC) AS RowNum FROM "PURCHASE_ORDER_DETAILS" AS "PURCHASE_ORDER_DETAILS" LEFT OUTER JOIN "PRODUCTS" AS PRODUCTS_231 ON "PURCHASE_ORDER_DETAILS"."PRODUCT_ID" = PRODUCTS_231."ID" GROUP BY PRODUCTS_231."PRODUCT_NAME", "PURCHASE_ORDER_DETAILS"."PRODUCT_ID") AS b WHERE RowNum >= 1 AND RowNum < 11 ORDER BY RowNum
127.0.0.1 - - [01/May/2024 14:39:56] "POST /api HTTP/1.1" 200 -


SELECT b."TOTAL_PRICE", PRODUCT_CATEGORY_LOOKUP FROM (SELECT SUM("ORDER_DETAILS"."TOTAL_PRICE") AS "TOTAL_PRICE", PRODUCTS_218."CATEGORY" AS PRODUCT_CATEGORY_LOOKUP, ROW_NUMBER() OVER ( ORDER BY sum("ORDER_DETAILS"."TOTAL_PRICE") DESC) AS RowNum FROM "ORDER_DETAILS" AS "ORDER_DETAILS" LEFT OUTER JOIN "PRODUCTS" AS PRODUCTS_218 ON "ORDER_DETAILS"."PRODUCT_ID" = PRODUCTS_218."ID" GROUP BY PRODUCTS_218."CATEGORY", "ORDER_DETAILS"."PRODUCT_CATEGORY") AS b WHERE RowNum >= 1 AND RowNum < 11 ORDER BY RowNum

The second is not.

The first table has product_id as lookup field.
The second table has product_id as MASTER field for product_category. Chart is:

    var ord = item.task.purchase_order_details.copy({handlers: false});
    ord.open(
        {
            fields: [ 'product_id', 'quantity'], 
            funcs: {quantity: 'sum'},
            group_by: ['product_id'],
            order_by: ['-quantity'],
            limit: 10
        }, 

    var acc = item.task.order_details.copy({handlers: false});
    acc.open(
        {
            fields: ['product_category', 'total_price'], 
            funcs: {'total_price': 'sum'},
            group_by: ['product_category'],
            order_by: ['-total_price'],
            limit: 10

[platipusica]

Same with postgres:

Exception: column purchase_order_details.category does not exist
LINE 1: ...oducts_231."id" GROUP BY products_231."category", "purchase_...
                                                             ^

127.0.0.1 - - [02/May/2024 12:26:09] "POST /api HTTP/1.1" 200 -

[platipusica]

Adding to pull rq.