join_on tag value is assigned to WHERE instead of JOIN

[hsequeda]

Issue Description

When using the join_on tag in bun ORM to specify join conditions for a relationship, the condition is unexpectedly influencing the WHERE clause, leading to a full omission of main entity results if the relationship's condition isn't met. This behavior is contrary to typical SQL JOIN operations where the main table's data would be returned with NULLs for the related table's columns if the JOIN condition fails.

Example

Given two tables books and authors, with a join_on condition like authors.nationality = 'Canadian' within the books model, the current implementation results in zero results for a book query when no authors meet the condition. The anticipated result is to receive the book data with NULLs for the author columns.

Expected Behavior

The join_on condition should be applied within the JOIN clause, permitting the retrieval of books records regardless of whether the authors condition is met.

Current Behavior

No books records are returned when the join_on condition for authors is not satisfied, suggesting that the condition is being incorrectly placed in the WHERE clause.

Steps to Reproduce

1. Define books and authors models with a join_on condition that references a field from the authors table.
2. Execute a query on books that includes a join with authors where the join_on condition (e.g., authors.nationality = 'Canadian') does not hold true.
3. Observe that the result set is empty, contrary to expectations.

Environment

• bun ORM version: [v1.1.12]
• Go version: [1.21]
• Database and version: [postgres v15]

Additional Information

(Include logs, error messages, and any other context that might aid in diagnosing the issue.)

Suggested Resolution

If available, outline any potential solutions or workarounds you've considered or attempted.

[timberkeley]

Just came across this myself and the only way I found around the problem is to use Join/JoinOn and lots of error prone ColumnExpr.