What the developer wrote
Authored by Abdullahi Yunus
graph: use UNION for isPublicNode query
In this commit we updated the IsPublicV1Node query to use UNION
instead of OR, since sqlite struggles to efficiently use
multiple indexes in a single query involving OR conditions across
different columns.
We use UNION ALL since the query doesn't care about duplicates.
What changed, and why it matters
This commit rewrites a single database query so that it checks two possible relationships as separate sub-queries combined with UNION ALL, instead of using OR inside one JOIN condition. The goal is to make SQLite run the query faster by using its indexes better. The result returned to the application is unchanged, so this is a performance fix, not a security fix.
No security action required. Treat as a routine performance optimization. If reviewing, verify the generated Go file matches the SQL source and that the UNION ALL does not alter query semantics under EXISTS.
Security signals we found
No strong security signals were identified.
Evidence from the diff
The IsPublicV1Node query previously joined graph_nodes to graph_channels with n.id = c.node_id_1 OR n.id = c.node_id_2. SQLite’s query planner can have difficulty combining multiple indexes across an OR condition, so the query was split into two identical sub-queries joined by UNION ALL: one matching node_id_1 and one matching node_id_2. Because the outer query is SELECT EXISTS(...), duplicates do not matter. The generated Go code in sqldb/sqlc/graph.sql.go mirrors the SQL change. No input handling, privilege checks, or logic semantics changed.
Changed components
sqldb/sqlc/queries/graph.sqlsqldb/sqlc/graph.sql.goIsPublicV1Node queryInspect captured patch +16 / −2
diff --git a/sqldb/sqlc/graph.sql.go b/sqldb/sqlc/graph.sql.go
index 8ed9333..0ce7780 100644
--- a/sqldb/sqlc/graph.sql.go
+++ b/sqldb/sqlc/graph.sql.go
@@ -2653,7 +2653,7 @@ const isPublicV1Node = `-- name: IsPublicV1Node :one
SELECT EXISTS (
SELECT 1
FROM graph_channels c
- JOIN graph_nodes n ON n.id = c.node_id_1 OR n.id = c.node_id_2
+ JOIN graph_nodes n ON n.id = c.node_id_1
-- NOTE: we hard-code the version here since the clauses
-- here that determine if a node is public is specific
-- to the V1 gossip protocol. In V1, a node is public
@@ -2665,6 +2665,13 @@ SELECT EXISTS (
WHERE c.version = 1
AND c.bitcoin_1_signature IS NOT NULL
AND n.pub_key = $1
+ UNION ALL
+ SELECT 1
+ FROM graph_channels c
+ JOIN graph_nodes n ON n.id = c.node_id_2
+ WHERE c.version = 1
+ AND c.bitcoin_1_signature IS NOT NULL
+ AND n.pub_key = $1
)
`
diff --git a/sqldb/sqlc/queries/graph.sql b/sqldb/sqlc/queries/graph.sql
index b9bee18..a8ff040 100644
--- a/sqldb/sqlc/queries/graph.sql
+++ b/sqldb/sqlc/queries/graph.sql
@@ -77,7 +77,7 @@ LIMIT $3;
SELECT EXISTS (
SELECT 1
FROM graph_channels c
- JOIN graph_nodes n ON n.id = c.node_id_1 OR n.id = c.node_id_2
+ JOIN graph_nodes n ON n.id = c.node_id_1
-- NOTE: we hard-code the version here since the clauses
-- here that determine if a node is public is specific
-- to the V1 gossip protocol. In V1, a node is public
@@ -89,6 +89,13 @@ SELECT EXISTS (
WHERE c.version = 1
AND c.bitcoin_1_signature IS NOT NULL
AND n.pub_key = $1
+ UNION ALL
+ SELECT 1
+ FROM graph_channels c
+ JOIN graph_nodes n ON n.id = c.node_id_2
+ WHERE c.version = 1
+ AND c.bitcoin_1_signature IS NOT NULL
+ AND n.pub_key = $1
);
-- name: DeleteUnconnectedNodes :many
Why this scored 19/100
Community notes
Notes can correct, qualify, or add evidence to the AI analysis. Every note shown here has been validated by a human moderator.
The AI analysis stands alone for now. Submit a note if you can add evidence or important context.