When I try to create a new Purchase Invoice (ERPNext dockerized version 14) and try to get the items from a Purchase Order I get the following error:
(The same goes for a custom link field, when clicked it tries to fetch the Purchase Order tickets and fails with the same error. However, it only affects the doctype Purchase Order itself.)
Here is the whole error:
App Versions
{
"erpnext": "14.44.0",
"frappe": "14.52.0"
}
Route
Form/Purchase Invoice/new-purchase-invoice-1
Traceback
Syntax error in query:
select `tabPurchase Order`.`schedule_date`, `tabPurchase Order`. as `label`, `tabPurchase Order`.`name`, `tabPurchase Order`.`supplier`, `tabPurchase Order`.`status`, `tabPurchase Order`.`transaction_date`, `tabPurchase Order`.`grand_total`, locate('', `tabPurchase Order`.`name`) as `_relevance`
from `tabPurchase Order`
where `tabPurchase Order`.`docstatus` = 1.0 and coalesce(`tabPurchase Order`.`status`, '') not in ('Closed', 'On Hold') and `tabPurchase Order`.`per_billed` < 99.99 and `tabPurchase Order`.`company` = 'Secret' and `tabPurchase Order`.`supplier` = 'Amazon'
order by _relevance, `tabPurchase Order`.docstatus asc, `tabPurchase Order`.`modified` DESC, `tabPurchase Order`.idx desc
limit 25 offset 0
Traceback (most recent call last):
File "apps/frappe/frappe/app.py", line 95, in application
response = frappe.api.handle()
File "apps/frappe/frappe/api.py", line 54, in handle
return frappe.handler.handle()
File "apps/frappe/frappe/handler.py", line 47, 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 1622, in call
return fn(*args, **newargs)
File "apps/frappe/frappe/desk/search.py", line 214, in search_widget
values = frappe.get_list(
File "apps/frappe/frappe/__init__.py", line 1911, in get_list
return frappe.model.db_query.DatabaseQuery(doctype).execute(*args, **kwargs)
File "apps/frappe/frappe/model/db_query.py", line 196, in execute
result = self.build_and_run()
File "apps/frappe/frappe/model/db_query.py", line 236, in build_and_run
return frappe.db.sql(
File "apps/frappe/frappe/database/database.py", line 220, in sql
self._cursor.execute(query, values)
File "env/lib/python3.10/site-packages/pymysql/cursors.py", line 158, in execute
result = self._query(query)
File "env/lib/python3.10/site-packages/pymysql/cursors.py", line 325, in _query
conn.query(q)
File "env/lib/python3.10/site-packages/pymysql/connections.py", line 549, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "env/lib/python3.10/site-packages/pymysql/connections.py", line 779, in _read_query_result
result.read()
File "env/lib/python3.10/site-packages/pymysql/connections.py", line 1157, in read
first_packet = self.connection._read_packet()
File "env/lib/python3.10/site-packages/pymysql/connections.py", line 729, 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 'as `label`, `tabPurchase Order`.`name`, `tabPurchase Order`.`supplier`, `tabP...' at line 1")
Request Data
{
"type": "GET",
"args": {
"doctype": "Purchase Order",
"txt": "",
"filters": "{\"docstatus\":1,\"status\":[\"not in\",[\"Closed\",\"On Hold\"]],\"per_billed\":[\"<\",99.99],\"company\":\"Secret\",\"supplier\":\"Amazon\"}",
"filter_fields": "[\"supplier\",\"schedule_date\"]",
"page_length": 25,
"as_dict": 1
},
"headers": {},
"error_handlers": {},
"url": "/api/method/frappe.desk.search.search_widget"
}
Response Data
{
"exception": "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 'as `label`, `tabPurchase Order`.`name`, `tabPurchase Order`.`supplier`, `tabP...' at line 1\")"
}
Does anybody have a solution for it?
