Aggregation expression should appear in group by clause

[roman4e]

Information about bug

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

Database postgresql v14

Steps to reproduce

Open Projects
Create project with any name like Project1
Save it
Reload page
after adding debug output, got such query:

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");

I also found it source:

erpnext/erpnext/projects/doctype/project/project.py

Line 367 in 4cea34c

.groupby(Date(timesheet_detail.from_time))

Should be:

.groupby(UnixTimestamp(timesheet_detail.from_time))

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)

Module

projects

Version

Installed frappe is v15.33.3
Installed erpnext is v15.29.2

Installation method

manual install

Relevant log output / Stack trace / Full Error Message.

19:54:33 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")                
19:54:33 web.1      |   File "/usr/lib/python3.10/threading.py", line 973, in _bootstrap                                
19:54:33 web.1      |     self._bootstrap_inner()                                                                       
19:54:33 web.1      |   File "/usr/lib/python3.10/threading.py", line 1016, in _bootstrap_inner                         
19:54:33 web.1      |     self.run()                                                                                    
19:54:33 web.1      |   File "/usr/lib/python3.10/threading.py", line 953, in run                                       
19:54:33 web.1      |     self._target(*self._args, **self._kwargs)                                                     
19:54:33 web.1      |   File "/usr/lib/python3.10/socketserver.py", line 683, in process_request_thread                 
19:54:33 web.1      |     self.finish_request(request, client_address)                                                  
19:54:33 web.1      |   File "/usr/lib/python3.10/socketserver.py", line 360, in finish_request                         
19:54:33 web.1      |     self.RequestHandlerClass(request, client_address, self)                                       
19:54:33 web.1      |   File "/usr/lib/python3.10/socketserver.py", line 747, in __init__                               
19:54:33 web.1      |     self.handle()                                                                                 
19:54:33 web.1      |   File "/srv/erpnext/www/erp/env/lib/python3.10/site-packages/werkzeug/serving.py", line 391, in h
andle                                                                                                                   
19:54:33 web.1      |     super().handle()                                                                              
19:54:33 web.1      |   File "/usr/lib/python3.10/http/server.py", line 433, in handle                                  
19:54:33 web.1      |     self.handle_one_request()                                                                     
19:54:33 web.1      |   File "/usr/lib/python3.10/http/server.py", line 421, in handle_one_request                      
19:54:33 web.1      |     method()                                                                                      
19:54:33 web.1      |   File "/srv/erpnext/www/erp/env/lib/python3.10/site-packages/werkzeug/serving.py", line 363, in r
un_wsgi                                                                                                                 
19:54:33 web.1      |     execute(self.server.app)                                                                      
19:54:33 web.1      |   File "/srv/erpnext/www/erp/env/lib/python3.10/site-packages/werkzeug/serving.py", line 326, in e
xecute                                                                                                                  
19:54:33 web.1      |     for data in application_iter:                                                                 
19:54:33 web.1      |   File "/srv/erpnext/www/erp/env/lib/python3.10/site-packages/werkzeug/debug/__init__.py", line 34
1, in debug_application                                                                                                 
19:54:33 web.1      |     app_iter = self.app(environ, start_response)                                                  
19:54:33 web.1      |   File "/srv/erpnext/www/erp/apps/frappe/frappe/middlewares.py", line 16, in __call__             
19:54:33 web.1      |     return super().__call__(environ, start_response)                                              
19:54:33 web.1      |   File "/srv/erpnext/www/erp/env/lib/python3.10/site-packages/werkzeug/middleware/shared_data.py",
 line 249, in __call__                                                                                                  
19:54:33 web.1      |     return self.app(environ, start_response)                                                      
19:54:33 web.1      |   File "/srv/erpnext/www/erp/env/lib/python3.10/site-packages/werkzeug/middleware/shared_data.py",
 line 249, in __call__ 
19:54:33 web.1      |     return self.app(environ, start_response)                                                      
19:54:33 web.1      |   File "/srv/erpnext/www/erp/apps/frappe/frappe/app.py", line 79, in application                  
19:54:33 web.1      |     app(environ, start_response),                                                                 
19:54:33 web.1      |   File "/srv/erpnext/www/erp/env/lib/python3.10/site-packages/werkzeug/wrappers/request.py", line 
190, in application                                                                                                     
19:54:33 web.1      |     resp = f(*args[:-2] + (request,))                                                             
19:54:33 web.1      |   File "/srv/erpnext/www/erp/apps/frappe/frappe/app.py", line 114, in application                 
19:54:33 web.1      |     response = frappe.api.handle(request)                                                         
19:54:33 web.1      |   File "/srv/erpnext/www/erp/apps/frappe/frappe/api/__init__.py", line 49, in handle              
19:54:33 web.1      |     data = endpoint(**arguments)                                                                  
19:54:33 web.1      |   File "/srv/erpnext/www/erp/apps/frappe/frappe/api/v1.py", line 36, in handle_rpc_call           
19:54:33 web.1      |     return frappe.handler.handle()                                                                
19:54:33 web.1      |   File "/srv/erpnext/www/erp/apps/frappe/frappe/handler.py", line 49, in handle                   
19:54:33 web.1      |     data = execute_cmd(cmd)                                                                       
19:54:33 web.1      |   File "/srv/erpnext/www/erp/apps/frappe/frappe/handler.py", line 85, in execute_cmd              
19:54:33 web.1      |     return frappe.call(method, **frappe.form_dict)                                                
19:54:33 web.1      |   File "/srv/erpnext/www/erp/apps/frappe/frappe/__init__.py", line 1808, in call                  
19:54:33 web.1      |     return fn(*args, **newargs)                                                                   
19:54:33 web.1      |   File "/srv/erpnext/www/erp/apps/frappe/frappe/utils/typing_validations.py", line 32, in wrapper 
19:54:33 web.1      |     return func(*args, **kwargs)                                                                  
19:54:33 web.1      |   File "/srv/erpnext/www/erp/apps/frappe/frappe/__init__.py", line 957, in wrapper_fn             
19:54:33 web.1      |     retval = fn(*args, **get_newargs(fn, kwargs))                                                 19:54:33 web.1      |   File "/srv/erpnext/www/erp/apps/frappe/frappe/desk/notifications.py", line 298, in get_open_coun
t                                                           
19:54:33 web.1      |     out["timeline_data"] = module.get_timeline_data(doctype, name)                                19:54:33 web.1      |   File "/srv/erpnext/www/erp/apps/erpnext/erpnext/projects/doctype/project/project.py", line 368, 
in get_timeline_data                                                                                                    
19:54:33 web.1      |     .run()                                                                                        
19:54:33 web.1      |   File "/srv/erpnext/www/erp/apps/frappe/frappe/query_builder/utils.py", line 84, in execute_query
19:54:33 web.1      |     result = frappe.db.sql(query, params, *args, **kwargs)  # nosemgrep                           
19:54:33 web.1      |   File "/srv/erpnext/www/erp/apps/frappe/frappe/database/postgres/database.py", line 221, in sql  
19:54:33 web.1      |     return super().sql(modify_query(query), modify_values(values), *args, **kwargs)               
19:54:33 web.1      |   File "/srv/erpnext/www/erp/apps/frappe/frappe/database/database.py", line 258, in sql           
19:54:33 web.1      |     traceback.print_stack()                                                                       
19:54:33 web.1      | Error in query:                                                                                   
19:54:33 web.1      | column "tabTimesheet Detail.from_time" must appear in the GROUP BY clause or be used in an aggrega
te function      
19:54:33 web.1      | 127.0.0.1 - - [08/Jul/2024 19:54:33] "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
48938283 HTTP/1.0" 500 -