Aggregation expression should appear in group by clause

[roman4e]

Description of the issue

Invalid grouping operation, field used in aggregation operation should appear in group by clause.

Context information (for bug reports)

Postgres database is used, latest.

Output of bench version

erpnext 14.x.x-develop
frappe 15.x.x-develop
payments 0.0.1

Installed frappe is v15.33.3
Installed erpnext is v14.70.9

Steps to reproduce the issue

1. Open Projects
2. Create project with any name like Project1
3. Save it
4. Reload page

Observed result

Error message popup

Expected result

No error and information should appear on project page

Stacktrace / full error message

20:26:48 web.1      | PG:QUERY=SELECT EXTRACT(epoch FROM "from_time"),COUNT(*) FROM "tabTimesheet Detail" WHERE "project
"=%(param1)s AND "from_time">CURRENT_DATE-INTERVAL '1 YEAR' AND "docstatus"<2 GROUP BY DATE("from_time")                
20:26:48 web.1      |   File "/usr/lib/python3.10/threading.py", line 973, in _bootstrap                                
20:26:48 web.1      |     self._bootstrap_inner()                                                                       
20:26:48 web.1      |   File "/usr/lib/python3.10/threading.py", line 1016, in _bootstrap_inner                         
20:26:48 web.1      |     self.run()                                                                                    
20:26:48 web.1      |   File "/usr/lib/python3.10/threading.py", line 953, in run                                       
20:26:48 web.1      |     self._target(*self._args, **self._kwargs)                                                     
20:26:48 web.1      |   File "/usr/lib/python3.10/socketserver.py", line 683, in process_request_thread                 
20:26:48 web.1      |     self.finish_request(request, client_address)                                                  
20:26:48 web.1      |   File "/usr/lib/python3.10/socketserver.py", line 360, in finish_request                         
20:26:48 web.1      |     self.RequestHandlerClass(request, client_address, self)                                       
20:26:48 web.1      |   File "/usr/lib/python3.10/socketserver.py", line 747, in __init__                               
20:26:48 web.1      |     self.handle()                                                                                 
20:26:48 web.1      |   File "/srv/erpnext/www/erp/env/lib/python3.10/site-packages/werkzeug/serving.py", line 391, in h
andle                                                                                                                   
20:26:48 web.1      |     super().handle()                                                                              
20:26:48 web.1      |   File "/usr/lib/python3.10/http/server.py", line 433, in handle                                  
20:26:48 web.1      |     self.handle_one_request()                                                                     
20:26:48 web.1      |   File "/usr/lib/python3.10/http/server.py", line 421, in handle_one_request                      
20:26:48 web.1      |     method()                                                                                      
20:26:48 web.1      |   File "/srv/erpnext/www/erp/env/lib/python3.10/site-packages/werkzeug/serving.py", line 363, in r
un_wsgi                                                                                                                 
20:26:48 web.1      |     execute(self.server.app)                                                                      
20:26:48 web.1      |   File "/srv/erpnext/www/erp/env/lib/python3.10/site-packages/werkzeug/serving.py", line 326, in e
xecute                                                                                                                  
20:26:48 web.1      |     for data in application_iter:                                                                 
20:26:48 web.1      |   File "/srv/erpnext/www/erp/env/lib/python3.10/site-packages/werkzeug/debug/__init__.py", line 34
1, in debug_application                                                                                                 
20:26:48 web.1      |     app_iter = self.app(environ, start_response)                                                  
20:26:48 web.1      |   File "/srv/erpnext/www/erp/apps/frappe/frappe/middlewares.py", line 16, in __call__             
20:26:48 web.1      |     return super().__call__(environ, start_response)                                              
20:26:48 web.1      |   File "/srv/erpnext/www/erp/env/lib/python3.10/site-packages/werkzeug/middleware/shared_data.py",
 line 249, in __call__                                                                                                  
20:26:48 web.1      |     return self.app(environ, start_response)                                                      
20:26:48 web.1      |   File "/srv/erpnext/www/erp/env/lib/python3.10/site-packages/werkzeug/middleware/shared_data.py",
 line 249, in __call__ 
20:26:48 web.1      |     return self.app(environ, start_response)                                                      
20:26:48 web.1      |   File "/srv/erpnext/www/erp/apps/frappe/frappe/app.py", line 79, in application                  
20:26:48 web.1      |     app(environ, start_response),                                                                 
20:26:48 web.1      |   File "/srv/erpnext/www/erp/env/lib/python3.10/site-packages/werkzeug/wrappers/request.py", line 
190, in application                                                                                                     
20:26:48 web.1      |     resp = f(*args[:-2] + (request,))                                                             
20:26:48 web.1      |   File "/srv/erpnext/www/erp/apps/frappe/frappe/app.py", line 114, in application                 
20:26:48 web.1      |     response = frappe.api.handle(request)                                                         
20:26:48 web.1      |   File "/srv/erpnext/www/erp/apps/frappe/frappe/api/__init__.py", line 49, in handle              
20:26:48 web.1      |     data = endpoint(**arguments)                                                                  
20:26:48 web.1      |   File "/srv/erpnext/www/erp/apps/frappe/frappe/api/v1.py", line 36, in handle_rpc_call           
20:26:48 web.1      |     return frappe.handler.handle()                                                                
20:26:48 web.1      |   File "/srv/erpnext/www/erp/apps/frappe/frappe/handler.py", line 49, in handle                   
20:26:48 web.1      |     data = execute_cmd(cmd)                                                                       
20:26:48 web.1      |   File "/srv/erpnext/www/erp/apps/frappe/frappe/handler.py", line 85, in execute_cmd              
20:26:48 web.1      |     return frappe.call(method, **frappe.form_dict)                                                
20:26:48 web.1      |   File "/srv/erpnext/www/erp/apps/frappe/frappe/__init__.py", line 1808, in call                  
20:26:48 web.1      |     return fn(*args, **newargs)                                                                   
20:26:48 web.1      |   File "/srv/erpnext/www/erp/apps/frappe/frappe/utils/typing_validations.py", line 32, in wrapper 
20:26:48 web.1      |     return func(*args, **kwargs)                                                                  
20:26:48 web.1      |   File "/srv/erpnext/www/erp/apps/frappe/frappe/__init__.py", line 957, in wrapper_fn             
20:26:48 web.1      |     retval = fn(*args, **get_newargs(fn, kwargs))                                                 20:26:48 web.1      |   File "/srv/erpnext/www/erp/apps/frappe/frappe/desk/notifications.py", line 298, in get_open_coun
t                                                           
20:26:48 web.1      |     out["timeline_data"] = module.get_timeline_data(doctype, name)                                20:26:48 web.1      |   File "/srv/erpnext/www/erp/apps/erpnext/erpnext/projects/doctype/project/project.py", line 368, 
in get_timeline_data                                                                                                    
20:26:48 web.1      |     .run()                                                                                        
20:26:48 web.1      |   File "/srv/erpnext/www/erp/apps/frappe/frappe/query_builder/utils.py", line 84, in execute_query
20:26:48 web.1      |     result = frappe.db.sql(query, params, *args, **kwargs)  # nosemgrep                           
20:26:48 web.1      |   File "/srv/erpnext/www/erp/apps/frappe/frappe/database/postgres/database.py", line 221, in sql  
20:26:48 web.1      |     return super().sql(modify_query(query), modify_values(values), *args, **kwargs)               20:26:48 web.1      |   File "/srv/erpnext/www/erp/apps/frappe/frappe/database/database.py", line 258, in sql           
20:26:48 web.1      |     traceback.print_stack()                                                                       
20:26:48 web.1      | Error in query:                                                                                   
20:26:48 web.1      | column "tabTimesheet Detail.from_time" must appear in the GROUP BY clause or be used in an aggrega
te function      
20:26:48 web.1      | 127.0.0.1 - - [07/Jul/2024 19:35:17] "GET /api/method/frappe.desk.notifications.get_open_count?doc
type=Project&name=Project1&items=["Task","Timesheet","Issue","Project%20Update","Material%20Request","BOM","Stock%20Entry","
Sales%20Order","Delivery%20Note","Sales%20Invoice","Purchase%20Order","Purchase%20Receipt","Purchase%20Invoice"]&_=17203
54778797 HTTP/1.0" 500 -

Additional information

Full SQL looks like:

SELECT EXTRACT(epoch FROM "from_time"),COUNT(*)
FROM "tabTimesheet Detail"
WHERE
    "project"='Project1'
  AND "from_time">CURRENT_DATE-INTERVAL '1 YEAR'
  AND "docstatus"< '2'
GROUP BY DATE("from_time");

A part EXTRACT(epoch FROM "from_time") should replace DATE("from_time") at GROUP BY

OS version / distribution, Frappe install method, etc.
Os Ubuntu 22.02, Frappe manual installation, Applied patch to PyPika terms file which fixes CURRENT_DATE, CURRENT_TIME error (kayak/pypika#809)

[roman4e]

It is totally erpnext related issue. Closing.
frappe/erpnext#42234