Hi,
I found a bug with PGSQL V18
Covers:
# IF(cond, val, NULL) → CASE WHEN cond THEN val ELSE NULL END
# IFNULL(a, b) → COALESCE(a, b)
# GROUP BY fixes → add missing columns for PostgreSQL strict mode
# t4.default_currency → must be in GROUP BY (common suffix in trends.py)
Fix :
import sys
import re
import os
# ---------------------------------------------------------------------------
# Patch: fix ERPNext MySQL-specific SQL functions → PostgreSQL equivalents
# ---------------------------------------------------------------------------
# Covers:
# IF(cond, val, NULL) → CASE WHEN cond THEN val ELSE NULL END
# IFNULL(a, b) → COALESCE(a, b)
# GROUP BY fixes → add missing columns for PostgreSQL strict mode
# t4.default_currency → must be in GROUP BY (common suffix in trends.py)
# ---------------------------------------------------------------------------
# SQL indicators — only regex-patch lines that contain at least one of these
_SQL_INDICATORS = (
"frappe.db.sql", "SUM(", "SELECT ", " WHERE ", "BETWEEN",
"GROUP BY", "IFNULL", "ifnull", "IF(", "GROUP_CONCAT",
)
# Regex-based replacements applied line-by-line
_REPLACEMENTS = [
\# SUM(IF(cond, val, NULL)) — trend-style periodic queries
(
re.compile(
r'SUM\\(IF\\((\[^,\]+),\\s\*(\[^,\]+),\\s\*NULL\\)\\)',
re.IGNORECASE,
),
r'SUM(CASE WHEN \\1 THEN \\2 ELSE NULL END)',
"SUM(IF(...,val,NULL)) → SUM(CASE WHEN ... THEN val END)",
),
\# Generic IF(cond, val1, val2) simple form (no parens in condition)
(
re.compile(
r'\\bIF\\((\[^,(\]+),\\s\*(\[^,)\]+),\\s\*(\[^,)\]+)\\)',
re.IGNORECASE,
),
r'CASE WHEN \\1 THEN \\2 ELSE \\3 END',
"IF(cond,val1,val2) → CASE WHEN cond THEN val1 ELSE val2 END",
),
\# IFNULL(a, b) → COALESCE(a, b) (covers IFNULL + ifnull)
(
re.compile(r'\\bIFNULL\\s\*\\(', re.IGNORECASE),
'COALESCE(',
"IFNULL() → COALESCE()",
),
]
# Files targeted by regex-based patches
_TARGET_FILES = [
"erpnext/controllers/trends.py",
"erpnext/selling/report/sales_order_analysis/sales_order_analysis.py",
"erpnext/startup/boot.py",
"erpnext/stock/doctype/stock_reconciliation/stock_reconciliation.py",
]
def _build_literal_patches() → list:
"""
Build list of (rel_path, old_str, new_str) for exact string patches.
Using a function avoids backslash confusion in module-level constants.
"""
patches = \[\]
\# -- sales_order_analysis: IF() with IN() condition (commas inside)
patches.append((
"erpnext/selling/report/sales_order_analysis/sales_order_analysis.py",
"IF(so.status in ('Completed','To Bill'), 0, (SELECT delay_days))",
"CASE WHEN so.status in ('Completed','To Bill')"
" THEN 0 ELSE (SELECT delay_days) END",
))
\# -- trends.py GROUP BY fixes: PostgreSQL strict mode (MySQL permissive)
\# Item: item_name selected but not in GROUP BY
patches.append((
"erpnext/controllers/trends.py",
'based_on_details\["based_on_group_by"\] = "t2.item_code"',
'based_on_details\["based_on_group_by"\] = "t2.item_code, t2.item_name"',
))
\# Supplier: supplier_name + supplier_group selected, not in GROUP BY
patches.append((
"erpnext/controllers/trends.py",
'based_on_details\["based_on_group_by"\] = "t1.supplier"',
'based_on_details\["based_on_group_by"\]'
' = "t1.supplier, t1.supplier_name, t3.supplier_group"',
))
\# Common suffix: t4.default_currency selected for ALL based_on but not in GROUP BY
\# Pattern: the 3-line common block that appends currency to select+cols+tables
patches.append((
"erpnext/controllers/trends.py",
(
'\\tbased_on_details\["based_on_select"\] +='
' "t4.default_currency as currency,"\\n'
'\\tbased_on_details\["based_on_cols"\]'
'.append("Currency:Link/Currency:120")\\n'
'\\tbased_on_details\["addl_tables"\] += ", \`tabCompany\` t4"\\n'
),
(
'\\tbased_on_details\["based_on_select"\] +='
' "t4.default_currency as currency,"\\n'
'\\t# PATCH: PostgreSQL requires t4.default_currency in GROUP BY\\n'
'\\tbased_on_details\["based_on_group_by"\]'
' += ", t4.default_currency"\\n'
'\\tbased_on_details\["based_on_cols"\]'
'.append("Currency:Link/Currency:120")\\n'
'\\tbased_on_details\["addl_tables"\] += ", \`tabCompany\` t4"\\n'
),
))
return patches
def _patch_line(line: str) → tuple:
"""Apply regex replacements to a single line. Returns (new_line, changed)."""
if not any(ind in line for ind in \_SQL_INDICATORS):
return line, False
original = line
for pattern, replacement, \_ in \_REPLACEMENTS:
line = pattern.sub(replacement, line)
return line, line != original
def _patch_file_regex(fpath: str) → int:
"""Apply regex patches to a file. Returns number of lines changed."""
try:
with open(fpath, "r", encoding="utf-8") as fh:
lines = fh.readlines()
except FileNotFoundError:
print(f" \[SKIP\] Not found: {fpath}")
return 0
new_lines = \[\]
changed_count = 0
for i, line in enumerate(lines, 1):
new_line, changed = \_patch_line(line)
new_lines.append(new_line)
if changed:
changed_count += 1
print(f" L{i:4d}: {line.rstrip()\[:80\]}")
print(f" → {new_line.rstrip()\[:80\]}")
if changed_count > 0:
with open(fpath, "w", encoding="utf-8") as fh:
fh.writelines(new_lines)
return changed_count
def _patch_file_literal(fpath: str, old_str: str, new_str: str) → int:
"""Apply a literal string replacement to a file. Returns 1 if changed."""
try:
with open(fpath, "r", encoding="utf-8") as fh:
content = fh.read()
except FileNotFoundError:
print(f" \[SKIP\] Not found: {fpath}")
return 0
if old_str not in content:
return 0
content = content.replace(old_str, new_str, 1)
with open(fpath, "w", encoding="utf-8") as fh:
fh.write(content)
return 1
def apply(bench_path: str) → None:
"""Apply all patches to targeted ERPNext files."""
erpnext_root = os.path.join(bench_path, "apps", "erpnext")
if not os.path.isdir(erpnext_root):
print(
f"\[ERROR\] ERPNext not found at {erpnext_root}",
file=sys.stderr,
)
sys.exit(1)
total_files = 0
total_lines = 0
\# ── Phase 1: regex-based patches
for rel_path in \_TARGET_FILES:
fpath = os.path.join(erpnext_root, rel_path)
print(f"\\n\[PATCH\] {rel_path}")
changed = \_patch_file_regex(fpath)
if changed:
total_files += 1
total_lines += changed
print(f" → {changed} line(s) patched.")
else:
print(" → No changes (already patched or pattern not found).")
\# ── Phase 2: literal patches
for rel_path, old_str, new_str in \_build_literal_patches():
fpath = os.path.join(erpnext_root, rel_path)
print(f"\\n\[LITERAL-PATCH\] {rel_path}")
changed = \_patch_file_literal(fpath, old_str, new_str)
if changed:
total_files += 1
total_lines += changed
print(f" → Patched: {old_str\[:70\].strip()}")
else:
print(" → No changes (already patched or pattern not found).")
print(
f"\\n\[OK\] Patch complete: {total_files} file(s),"
f" {total_lines} line(s) modified."
)
if _name_ == “_main_”:
bench_path = (
sys.argv\[1\] if len(sys.argv) > 1 else "/home/frappe/frappe-bench"
)
apply(bench_path)