Grouping Error in Postres Setup

[Parth_Vashista]

This is such a big problem with Frappe and ERPNext. This emerges when you want to see a link field in a parent doctype in its list view.

App Versions

{
	"frappe": "15.8.1",
	"frappe_system_monitor": "0.0.1",
	"raven": "1.3.2"
}

Route

List/Raven Channel Member/List

Traceback

Error in query:
column "tabRaven Channel_1.channel_name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...user_id", "tabRaven Channel Member"."channel_id", "tabRaven ...
                                                             ^

Traceback (most recent call last):
  File "apps/frappe/frappe/app.py", line 110, in application
    response = frappe.api.handle(request)
               ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/api/__init__.py", line 49, in handle
    data = endpoint(**arguments)
           ^^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/api/v1.py", line 36, in handle_rpc_call
    return frappe.handler.handle()
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/handler.py", line 49, in handle
    data = execute_cmd(cmd)
           ^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/handler.py", line 85, in execute_cmd
    return frappe.call(method, **frappe.form_dict)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/__init__.py", line 1689, in call
    return fn(*args, **newargs)
           ^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/utils/typing_validations.py", line 31, in wrapper
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/__init__.py", line 854, in wrapper_fn
    retval = fn(*args, **get_newargs(fn, kwargs))
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/desk/reportview.py", line 28, in get
    data = compress(execute(**args), args=args)
                    ^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/desk/reportview.py", line 64, in execute
    return DatabaseQuery(doctype).execute(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/model/db_query.py", line 203, in execute
    result = self.build_and_run()
             ^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/model/db_query.py", line 243, in build_and_run
    return frappe.db.sql(
           ^^^^^^^^^^^^^^
  File "apps/frappe/frappe/database/postgres/database.py", line 203, in sql
    return super().sql(modify_query(query), modify_values(values), *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "apps/frappe/frappe/database/database.py", line 217, in sql
    self._cursor.execute(query, values)
psycopg2.errors.GroupingError: column "tabRaven Channel_1.channel_name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...user_id", "tabRaven Channel Member"."channel_id", "tabRaven ...
                                                             ^

Request Data

{
	"type": "POST",
	"args": {
		"doctype": "Raven Channel Member",
		"fields": "[\"`tabRaven Channel Member`.`name`\",\"`tabRaven Channel Member`.`owner`\",\"`tabRaven Channel Member`.`creation`\",\"`tabRaven Channel Member`.`modified`\",\"`tabRaven Channel Member`.`modified_by`\",\"`tabRaven Channel Member`.`_user_tags`\",\"`tabRaven Channel Member`.`_comments`\",\"`tabRaven Channel Member`.`_assign`\",\"`tabRaven Channel Member`.`_liked_by`\",\"`tabRaven Channel Member`.`docstatus`\",\"`tabRaven Channel Member`.`idx`\",\"`tabRaven Channel Member`.`user_id`\",\"`tabRaven Channel Member`.`channel_id`\",\"channel_id.channel_name as channel_id_channel_name\"]",
		"filters": "[]",
		"order_by": "`tabRaven Channel Member`.`modified` DESC",
		"start": 0,
		"page_length": 20,
		"view": "List",
		"group_by": "`tabRaven Channel Member`.`name`",
		"with_comment_count": 1
	},
	"freeze": false,
	"freeze_message": "Loading...",
	"headers": {},
	"error_handlers": {},
	"url": "/api/method/frappe.desk.reportview.get",
	"request_id": null
}

Response Data

{
	"exception": "",
	"exc_type": "GroupingError"
}

[Parth_Vashista]

@NCP @revant_one @rmehta This error occurs when we have the link field name set to int i.e: auto increment. I would love to help in fixing of this bug being a developer myself can you please look into this and assign someone to help out. In the core of RDBMS i want to keep the Ids of most of my masters as auto-increment but this is stopping me to do so and im sure many other. Indexing is getting hampered because of this.

[Parth_Vashista]

Server Error in List View

"I have two doctypes: WMC and WM. In WMC, there is a field called “weapon_category,” which is configured as a link field to WM and has been selected as the title field in WCM, as shown in the WCM.png image. Now, in the WM doctype, when I add the “weapon_category” field to the List Settings, I encounter a server error. However, if I don’t select the “weapon_category” field in the List Settings, there is no error. Also, if I uncheck “SHOW Title In link Field,” then it works fine. However, I need …

This has been an issue faced by users.

[rmehta]

@Parth_Vashista Postgres support is not first-class in the Frappe ecosystem.

This problem specifically is with the Raven app, please raise an issue on their GitHub

[Parth_Vashista]

Dear Rushab sir the problem is not with the raven app its is with how the postgres queries are being handled, I have raised the issue on frappe github and will raise a pull request after resolving it.