PostgreSQL syntax error during installation

hhharsha36 commented 6 months ago

Information about bug

I am using PostgreSQL version 16.1 and upon trying to install the latest version of hrms (15.6.0), I am getting a SQL syntax error.

Module

HR, other

Version

Frappe Version: 15.4.1 ERPNext Version: 15.5.0 HRMS Version: 15.6.0

Installation method

docker

Relevant log output / Stack trace / Full Error Message.

Syntax error in query:
UPDATE "tabSalary Detail" "sd" JOIN "tabSalary Structure" "ss" ON "ss"."name"="sd"."parent" SET "sd"."docstatus"= '1' WHERE "ss"."docstatus"= '1' AND "sd"."parenttype"=%(param1)s {'param1': 'Salary Structure'}
Installation for Frappe HR app failed due to an error. Please try re-installing the app or report the issue on https://github.com/frappe/hrms/issues/new if not resolved.
An error occurred while installing hrms: syntax error at or near "JOIN"
LINE 1: UPDATE "tabSalary Detail" "sd" JOIN "tabSalary Structure" "s...
                                       ^
Traceback (most recent call last):
  File "apps/frappe/frappe/commands/site.py", line 462, in install_app
    _install_app(app, verbose=context.verbose, force=force)
  File "apps/frappe/frappe/installer.py", line 311, in install_app
    frappe.get_attr(after_install)()
  File "apps/hrms/hrms/install.py", line 21, in after_install
    raise e
  File "apps/hrms/hrms/install.py", line 9, in after_install
    setup()
  File "apps/hrms/hrms/setup.py", line 24, in after_install
    run_post_install_patches()
  File "apps/hrms/hrms/setup.py", line 675, in run_post_install_patches
    frappe.get_attr(f"hrms.patches.post_install.{patch_name}.execute")()
  File "apps/hrms/hrms/patches/post_install/set_salary_details_submittable.py", line 14, in execute
    ).run()
      ^^^^^
  File "apps/frappe/frappe/query_builder/utils.py", line 87, in execute_query
    result = frappe.db.sql(query, params, *args, **kwargs)  # nosemgrep
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  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: syntax error at or near "JOIN"
LINE 1: UPDATE "tabSalary Detail" "sd" JOIN "tabSalary Structure" "s...

Code of Conduct

[X] I agree to follow this project's Code of Conduct

ratthapon commented 6 months ago

Are there any update solution?

hhharsha36 commented 6 months ago

Are there any update solution?

The only solution I could think of is rewriting the SQL query to be compatible with both Postgres and MariaDB

Sadly, I am not familiar with SQL queries to help with a PR

frappe / hrms