Closed alucryd closed 7 months ago
Clicking on Copy error to clipboard
produces the following.
{
"erpnext": "15.10.4",
"frappe": "15.10.0"
}
Form/Sales Order/new-sales-order-tkdmqjocgx
Error in query:
function if(boolean, numeric, unknown) does not exist
LINE 1: select t2.item_code, t2.item_name,SUM(IF(t1.transaction_date...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
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 855, in wrapper_fn
retval = fn(*args, **get_newargs(fn, kwargs))
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/desk/query_report.py", line 221, in run
result = generate_report_result(report, filters, user, custom_columns, is_tree, parent_field)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/__init__.py", line 855, in wrapper_fn
retval = fn(*args, **get_newargs(fn, kwargs))
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/desk/query_report.py", line 81, in generate_report_result
res = get_report_result(report, filters) or []
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/desk/query_report.py", line 62, in get_report_result
res = report.execute_script_report(filters)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/core/doctype/report/report.py", line 164, in execute_script_report
res = self.execute_module(filters)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/core/doctype/report/report.py", line 181, in execute_module
return frappe.get_attr(method_name)(frappe._dict(filters))
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "apps/erpnext/erpnext/selling/report/sales_order_trends/sales_order_trends.py", line 15, in execute
data = get_data(filters, conditions)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "apps/erpnext/erpnext/controllers/trends.py", line 192, in get_data
data = 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 235, in sql
self._cursor.execute(query, values)
psycopg2.errors.UndefinedFunction: function if(boolean, numeric, unknown) does not exist
LINE 1: select t2.item_code, t2.item_name,SUM(IF(t1.transaction_date...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
{
"type": "POST",
"args": {
"report_name": "Sales Order Trends",
"filters": "{\"period\":\"Monthly\",\"based_on\":\"Item\",\"company\":\"Test\",\"fiscal_year\":\"2024\"}",
"ignore_prepared_report": 1
},
"headers": {},
"error_handlers": {},
"url": "/api/method/frappe.desk.query_report.run",
"request_id": null
}
{
"exception": "",
"exc_type": "UndefinedFunction"
}
Fixing the query in trends.py
unearthed another error:
{
"erpnext": "15.10.5",
"frappe": "15.10.0"
}
Form/Sales Order/new-sales-order-vvodnhpqvg
Syntax error in query:
select
tabItem.name , item_name, item_group, customer_code, if(length(tabItem.description) > '40', concat(substr(tabItem.description, 1, 40), "..."), description) as description
from "tabItem"
where tabItem.docstatus < '2'
and tabItem.disabled= '0'
and tabItem.has_variants= '0'
and (tabItem.end_of_life > %(today)s or coalesce(tabItem.end_of_life, '0000-00-00')='0000-00-00')
and (item_name like %(txt)s or description like %(txt)s or item_group like %(txt)s or customer_code like %(txt)s or name like %(txt)s or item_code like %(txt)s or tabItem.item_code IN (select parent from "tabItem Barcode" where barcode LIKE %(txt)s)
or tabItem.description LIKE %(txt)s)
and "tabItem"."is_sales_item" = '1' and "tabItem"."has_variants" = '0'
order by
if(strpos( name, %(_txt)s), strpos( name, %(_txt)s), 99999),
if(strpos( item_name, %(_txt)s), strpos( item_name, %(_txt)s), 99999),
idx desc,
name, item_name
limit %(start)s, %(page_len)s {'today': '2024-01-25', 'txt': '%%', '_txt': '', 'start': '0', 'page_len': '10'}
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/desk/search.py", line 47, in search_link
results = search_widget(
^^^^^^^^^^^^^^
File "apps/frappe/frappe/utils/typing_validations.py", line 31, in wrapper
return func(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/desk/search.py", line 95, in search_widget
return frappe.call(
^^^^^^^^^^^^
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 2421, in wrapper
return fn(**kwargs)
^^^^^^^^^^^^
File "apps/erpnext/erpnext/controllers/queries.py", line 275, in item_query
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 235, in sql
self._cursor.execute(query, values)
psycopg2.errors.SyntaxError: LIMIT #,# syntax is not supported
LINE 16: limit '0', '10'
^
HINT: Use separate LIMIT and OFFSET clauses.
{
"type": "POST",
"args": {
"txt": "",
"doctype": "Item",
"ignore_user_permissions": 0,
"reference_doctype": "Sales Order Item",
"query": "erpnext.controllers.queries.item_query",
"filters": "{\"is_sales_item\":1,\"customer\":\"Test\",\"has_variants\":0}"
},
"headers": {},
"error_handlers": {},
"url": "/api/method/frappe.desk.search.search_link",
"request_id": null
}
{
"exception": "",
"exc_type": "SyntaxError"
}
Hi @alucryd,
ERPNext: v15.10.8 (version-15)
Frappe Framework: v15.11.0 (version-15)
We tested in the latest version, but we haven't faced any type of issue. so please upgrade your version and check it.
Thank You!
Hi, thank you for the reply. I had to fix quite a few queries in the code of the version I was using, each time I fixed one another error cropped up, and I gave up after a dozen fixes or so thinking maybe postgres wasn't officially supported yet. So I switched to mariadb, but it is abysmally slow (even after increasing the number of workers, and increasing the innodb buffer). I will try the latest version wth postgres again, thank you.
Still getting another error:
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 1684, 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/desk/search.py", line 47, in search_link
results = search_widget(
^^^^^^^^^^^^^^
File "apps/frappe/frappe/utils/typing_validations.py", line 31, in wrapper
return func(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^
File "apps/frappe/frappe/desk/search.py", line 95, in search_widget
return frappe.call(
^^^^^^^^^^^^
File "apps/frappe/frappe/__init__.py", line 1684, 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 2423, in wrapper
return fn(**kwargs)
^^^^^^^^^^^^
File "apps/erpnext/erpnext/controllers/queries.py", line 275, in item_query
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 235, in sql
self._cursor.execute(query, values)
psycopg2.errors.SyntaxError: LIMIT #,# syntax is not supported
LINE 16: limit '0', '10'
^
HINT: Use separate LIMIT and OFFSET clauses.
postgres isn't supported on erpnext yet.
Information about bug
When attempting to create a new sales order, I can't select any article in the list and always get the error below.
Module
selling
Version
15.10.4
Installation method
docker
Relevant log output / Stack trace / Full Error Message.