Skip to content

Commit fd812e9

Browse files
Bockitclaude
andcommitted
compiler: an outer join makes the joins nested under it nullable
isTableRequired decided each join from its own type alone. A join on the nullable side of an outer join is nullable as a whole, but `a LEFT JOIN b ... RIGHT JOIN c` reported a as never null, and so did `a LEFT JOIN (b JOIN c ON ...) ON ...` for b and c. Carry the nullable side down into nested joins. Co-Authored-By: Claude Fable 5.2 <[email protected]>
1 parent 6935674 commit fd812e9

7 files changed

Lines changed: 258 additions & 0 deletions

File tree

‎internal/compiler/output_columns.go‎

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -448,6 +448,11 @@ func isTableRequired(n ast.Node, col *Column, prior int) int {
448448
}
449449
case *ast.JoinExpr:
450450
helper := func(l, r int) int {
451+
// A join on the nullable side of an outer join is nullable as a
452+
// whole, whatever kind of join it is itself.
453+
if prior == tableOptional {
454+
l, r = tableOptional, tableOptional
455+
}
451456
if res := isTableRequired(n.Larg, col, l); res != tableNotFound {
452457
return res
453458
}

‎internal/endtoend/testdata/join_nested_outer/postgresql/pgx/go/db.go‎

Lines changed: 32 additions & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎internal/endtoend/testdata/join_nested_outer/postgresql/pgx/go/models.go‎

Lines changed: 28 additions & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎internal/endtoend/testdata/join_nested_outer/postgresql/pgx/go/query.sql.go‎

Lines changed: 143 additions & 0 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.
Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,22 @@
1+
-- name: LeftThenRight :many
2+
SELECT t.plate, k.active, d.name
3+
FROM trucks t
4+
LEFT JOIN trackers k ON k.truck_id = t.id
5+
RIGHT JOIN depots d ON d.id = t.depot_id;
6+
7+
-- name: InnerThenFull :many
8+
SELECT t.plate, k.active, d.name
9+
FROM trucks t
10+
JOIN trackers k ON k.truck_id = t.id
11+
FULL JOIN depots d ON d.id = t.depot_id;
12+
13+
-- name: ParenthesisedJoin :many
14+
SELECT d.name, t.plate, k.active
15+
FROM depots d
16+
LEFT JOIN (trucks t JOIN trackers k ON k.truck_id = t.id) ON t.depot_id = d.id;
17+
18+
-- name: LeftThenLeft :many
19+
SELECT d.name, t.plate, k.active
20+
FROM depots d
21+
LEFT JOIN trucks t ON t.depot_id = d.id
22+
LEFT JOIN trackers k ON k.truck_id = t.id;
Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,18 @@
1+
CREATE TABLE depots (
2+
id BIGSERIAL PRIMARY KEY,
3+
name text NOT NULL,
4+
note text
5+
);
6+
7+
CREATE TABLE trucks (
8+
id BIGSERIAL PRIMARY KEY,
9+
depot_id bigint NOT NULL,
10+
plate text NOT NULL
11+
);
12+
13+
CREATE TABLE trackers (
14+
id BIGSERIAL PRIMARY KEY,
15+
truck_id bigint NOT NULL,
16+
active boolean NOT NULL,
17+
serial text
18+
);
Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,10 @@
1+
version: "2"
2+
sql:
3+
- engine: "postgresql"
4+
schema: "schema.sql"
5+
queries: "query.sql"
6+
gen:
7+
go:
8+
package: "querytest"
9+
out: "go"
10+
sql_package: "pgx/v5"

0 commit comments

Comments
 (0)