frappe / erpnext

Free and Open Source Enterprise Resource Planning (ERP)
https://erpnext.com
GNU General Public License v3.0
21.87k stars 7.33k forks source link

PostgresDB error on query #36836

Closed sinhpn92 closed 1 year ago

sinhpn92 commented 1 year ago

Information about bug

I have got the error when using PostgresDB.

The query is not support Postgres. Pls check the log.

Module

stock

Version

Frappe Version: 14.46.0 ERPNext Version: 14.36.0

Installation method

None

Relevant log output / Stack trace / Full Error Message.

### App Versions

{
    "erpnext": "14.36.0",
    "frappe": "14.46.0"
}

Route

Form/Stock Entry/new-stock-entry-1

Traceback

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_stock_item" = '1' 
        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': '2023-08-27', 'txt': '%%', '_txt': '', 'start': '0', 'page_len': '20'}
Traceback (most recent call last):
  File "apps/frappe/frappe/app.py", line 94, 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 37, in search_link
    search_widget(
  File "apps/frappe/frappe/desk/search.py", line 108, in search_widget
    raise e
  File "apps/frappe/frappe/desk/search.py", line 85, in search_widget
    frappe.response["values"] = frappe.call(
  File "apps/frappe/frappe/__init__.py", line 1622, in call
    return fn(*args, **newargs)
  File "apps/frappe/frappe/__init__.py", line 2437, in wrapper
    return fn(**kwargs)
  File "apps/erpnext/erpnext/controllers/queries.py", line 263, in item_query
    return frappe.db.sql(
  File "apps/frappe/frappe/database/postgres/database.py", line 199, in sql
    return super().sql(modify_query(query), modify_values(values), *args, **kwargs)
  File "apps/frappe/frappe/database/database.py", line 220, in sql
    self._cursor.execute(query, values)
psycopg2.errors.SyntaxError: LIMIT #,# syntax is not supported
LINE 16:   limit '0', '20'
           ^
HINT:  Use separate LIMIT and OFFSET clauses.

Request Data

{
    "type": "POST",
    "args": {
        "txt": "",
        "doctype": "Item",
        "ignore_user_permissions": 0,
        "reference_doctype": "Stock Entry Detail",
        "query": "erpnext.controllers.queries.item_query",
        "filters": "{\"is_stock_item\":1}"
    },
    "headers": {},
    "error_handlers": {},
    "url": "/api/method/frappe.desk.search.search_link"
}

Response Data

{
    "exception": ""
}
Tasztalos69 commented 11 months ago

I'm also getting this error.

Inside sales invoice creation, when trying to add an item, the error is:

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 1715, 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 1715, 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 2457, in wrapper
    return fn(**kwargs)
           ^^^^^^^^^^^^
  File "apps/erpnext/erpnext/controllers/queries.py", line 273, 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 217, 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.

Version

v15.6.1

Environment

Docker

Request

POST /api/method/frappe.desk.search.search_link

txt=""
doctype=Item
ignore_user_permissions=0
reference_doctype=Sales Invoice Item
query=erpnext.controllers.queries.item_query
filters={"is_sales_item":1,"customer":"<customer>","has_variants":0}