Relationship Cypher pushes down only a node scan and silently returns wrong rows

[prrao87]

Severity: high. Returns incorrect results with no error.

Environment

• PostgreSQL 18.4 (Homebrew), macOS arm64
• pg_ladybug main @ e5182153f1d4b588e7fd758d69c1406a6649a79f
• liblbug 0.19.1

Summary

For any Cypher query that traverses a relationship, ladybug.pushed_sql()
returns SQL for the first node table only; no join, no relationship scan,
no aggregate. ladybug.cypher() executes that partial SQL via SPI, and the
result-column mapper fills every unmatched requested column with NULL.

The caller gets one row per node containing NULLs, and no error.

Minimal reproduction

CREATE DATABASE bugrepro;
\c bugrepro

CREATE EXTENSION pg_ladybug;

CREATE TABLE node_city (
    id      bigint PRIMARY KEY,
    name    text NOT NULL,
    country text NOT NULL
);

CREATE TABLE rel_route (
    id       bigint PRIMARY KEY,
    src_id   bigint NOT NULL REFERENCES node_city(id),
    dst_id   bigint NOT NULL REFERENCES node_city(id),
    distance double precision NOT NULL
);

INSERT INTO node_city VALUES
    (1, 'Toronto',   'CA'),
    (2, 'Montreal',  'CA'),
    (3, 'Vancouver', 'CA');

INSERT INTO rel_route VALUES
    (1, 1, 2,  541.0),
    (2, 2, 3, 3679.0),
    (3, 1, 3, 3358.0),
    (4, 3, 1, 3358.0);

SELECT ladybug.register_node('City',  'node_city', 'id', NULL, 'demo');
SELECT ladybug.register_edge('Route', 'rel_route', 'src_id', 'dst_id', 'id', 'demo');

SET ladybug.storage_path = '/tmp/bugrepro.lbdb';
SET ladybug.pg_connstr   = 'host=/path/to/socket port=5432 dbname=bugrepro user=me';

Ground truth: 3 nodes, 4 edges.

Case 1 — count(*) over a relationship

SELECT * FROM ladybug.cypher(
  'MATCH (a:node_city)-[r:rel_route]->(b:node_city) RETURN count(*)'
) AS t(edge_count bigint);

Expected: one row, 4.

Actual:

 edge_count
------------

(3 rows -- one per node, all NULL)

Case 2 — relationship property projection

SELECT * FROM ladybug.cypher(
  'MATCH (a:node_city)-[r:rel_route]->(b:node_city) RETURN a.name, b.name, r.distance'
) AS t(src text, dst text, distance double precision);

Expected: 4 rows (Toronto|Montreal|541, …).

Actual: 3 rows of three NULLs.

The pushed-down SQL

SELECT ladybug.pushed_sql(
  'MATCH (a:node_city)-[r:rel_route]->(b:node_city) RETURN count(*)'
);

SELECT id,name,country FROM "public"."node_city"

No rel_route, no join, no count(*).

Contrast: node-only queries are correct

SELECT * FROM ladybug.cypher('MATCH (a:node_city) RETURN a.name, a.country')
  AS t(name text, country text);

   name    | country
-----------+---------
 Toronto   | CA
 Montreal  | CA
 Vancouver | CA

Root cause (as far as I can tell)

ladybug.explain() for the failing query shows a correct plan:
SCAN_REL_TABLE[1] on rel_route, two HASH_JOINs, and the projection. The
plan contains two TABLE_FUNCTION_CALL nodes, each emitting
SELECT id,name,country FROM "public"."node_city".

The extractor appears to take the first SQL-producing table function and treat
it as the SQL for the entire Ladybug plan. The bridge runs that fragment, and
the fuzzy result-column mapper then synthesizes NULL for the requested
columns it cannot match.

Suggested fix

1. Refuse pushdown unless the extracted SQL represents the complete logical
   query — relationship joins, filters, projection, aggregation, and limit.
   Failing closed with an error is far better than silently wrong rows.
2. Treat any missing or ambiguous output-column mapping as an error. Never
   synthesize NULL for a column the SQL did not produce.
3. Add end-to-end tests comparing Cypher results against equivalent SQL for
   relationship projection, count(*), filtered traversals, and multi-hop
   patterns.

[adsharma]

@prrao87 this is a documentation gap. Here's how the table registration works:

https://github.com/LadybugDB/extensions/blob/main/pg_client/src/catalog/pg_client_catalog.cpp#L68-L83

If you use the rel_ prefix, you're promising that the primary key is a CSR index. If it's a generic foreign key, you need to use the fkrel_ prefix.

I have this convention coded up for duckdb auto registration of tables:

LadybugDB/extensions#31

Open to further discussion and feedback on what's a good convention.