get_count api error for list view

[devarshi-007]

Description of the issue

When non authorize person see list this error comes.
For Ex. As an HR i have assigned and share Job Applicant to some interviewer. Then interviewer should able to see that Job Applicant. But as interviewer open Job Applicant List it see that Job Applicant but error come for sql query as shown in below image.

It should happen same for all documents. If I assign interview to some employee, so employee now able to see that interview but in list view it shows sql query error for get_count

I am using version-15

This issue doctypes are from hrms but this is happening because of frappe/model/db_query.py. I don't know exact reason, but in version-14 code it work fine. Error comes in version-15

Context information (for bug reports)

Output of bench version

5.17.0

Steps to reproduce the issue

1. Create a Job Applicant
2. Create a User and give role interviewer
3. Assign Job Applicant to that User (Interviewer)
4. Now open ERP as Interviewer and when you open Job Applicant list it shows some query error.

Observed result

Expected result

Stacktrace / full error message

Syntax error in query:
select distinct 
                  from `tabJob Applicant`
                  where `tabJob Applicant`.name in ('a@a.a') 
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 1682, 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 847, in wrapper_fn
  retval = fn(*args, **get_newargs(fn, kwargs))
File "apps/frappe/frappe/desk/reportview.py", line 68, in get_count
  data = execute(**args)[0].get("total_count")
File "apps/frappe/frappe/desk/reportview.py", line 77, in execute
  return DatabaseQuery(doctype).execute(*args, **kwargs)
File "apps/frappe/frappe/model/db_query.py", line 212, in execute
  result = self.build_and_run()
File "apps/frappe/frappe/model/db_query.py", line 259, in build_and_run
  return frappe.db.sql(
File "apps/frappe/frappe/database/database.py", line 217, in sql
  self._cursor.execute(query, values)
File "env/lib/python3.10/site-packages/pymysql/cursors.py", line 153, in execute
  result = self._query(query)
File "env/lib/python3.10/site-packages/pymysql/cursors.py", line 322, in _query
  conn.query(q)
File "env/lib/python3.10/site-packages/pymysql/connections.py", line 558, in query
  self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "env/lib/python3.10/site-packages/pymysql/connections.py", line 822, in _read_query_result
   result.read()
File "env/lib/python3.10/site-packages/pymysql/connections.py", line 1200, in read
   first_packet = self.connection._read_packet()
File "env/lib/python3.10/site-packages/pymysql/connections.py", line 772, in _read_packet
  packet.raise_for_error()
File "env/lib/python3.10/site-packages/pymysql/protocol.py", line 221, in raise_for_error
  err.raise_mysql_exception(self._data)
File "env/lib/python3.10/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
  raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from `tabJob Applicant`\n\t\t\twhere `tabJob Applicant`.name in ('a@a.a')' at line 2")
 
"POST /api/method/frappe.desk.reportview.get_count HTTP/1.1" 500 -

Additional information

OS version / distribution, Frappe install method, etc.

[ankush]

select distinct count(`tabJob Applicant`.name) as total_count
			from `tabJob Applicant`
			where `tabJob Applicant`.`name` in ('a@a.com')

Query should be this 😅 Count is somehow getting removed.

[ankush]

If you can debug code check what's happening here:

frappe/frappe/desk/reportview.py

Lines 111 to 113 in e203699

	if df.fieldname in [_df.fieldname for _df in meta.get_high_permlevel_fields()]:
	if df.get("permlevel") not in meta.get_permlevel_access(parenttype=data.doctype):
	data.fields.remove(field)

[devarshi-007]

I have checked it and logged all fieldname before entering in first if and data after executing if chain. When I access Job Applicant from admin and when I access Job Applicant as assigned to interviewer, In both case output will remain same so, I don't think, it is affecting query.

[devarshi-007]

I have observe that count field is removed from db_query.py -> apply_fieldlevel_read_permissions() function. It is calling get_permitted_fields() from frappe/model/init.py file and return [] box. So, that count query is getting removed.

Flow -> reportview.py file's get_count() is called -> it will call execute and it will call db_query.py file's execute() method -> execute() call build_and_run() -> prepare_args() -> it's first 5 function's last 5th line function apply_fieldlevel_read_permissions() -> It will call frappe/model/init.py's get_permitted_fields() and it will return [] so, count query is getting removed
@ankush

[ankush]

#23904