After gorm v.1.25.5 (so 1.25.6 upwards) nested Where's lead to different SQL output with Postgres (did not check other engines) and ultimately SQL that indirectly makes the Where useless.
SELECT companies.country, COUNT(DISTINCT users.name)
FROM "users"
LEFT JOIN companies ON users.company_id = companies.id
WHERE (users.name ILIKE '%%' OR companies.name ILIKE '%%')
AND users.something IN ('1')
GROUP BY "companies"."country";
Result: [{DE 1} {GB 1}]
Query v1.25.6+:
SELECT companies.country, COUNT(DISTINCT users.name)
FROM "users"
LEFT JOIN companies ON users.company_id = companies.id
WHERE users.name ILIKE '%%'
OR companies.name ILIKE '%%' AND (users.name ILIKE '%%' OR companies.name ILIKE '%%') AND users.something IN ('1')
GROUP BY "companies"."country";
Result: [{DE 2} {GB 1} {US 1}]
The test will likely only work with Postgres because of ILIKE usage, but switching that to LIKE should lead to the same result.
Explain your user case and expected results
After gorm
v.1.25.5
(so1.25.6
upwards) nestedWhere
's lead to different SQL output with Postgres (did not check other engines) and ultimately SQL that indirectly makes the Where useless.Go code:
Query v.1.25.5:
Result:
[{DE 1} {GB 1}]
Query v1.25.6+:
Result:
[{DE 2} {GB 1} {US 1}]
The test will likely only work with Postgres because of
ILIKE
usage, but switching that toLIKE
should lead to the same result.