This work was produced with the help of language models.

Ladybug version

0.22.0.dev20261016 (nightly build of main) and v0.21.2, PyPI ladybug wheels, Python 3.14.

What operating system are you using?

Ubuntu 24.04 (Linux x86_64)

What happened?

After WITH DISTINCT on a primary key, a second DISTINCT over coalesce, CASE or a list built from the carried value returns one row where there should be one per key: three nodes come back as a single [1] instead of [1], [2], [3], with no error. RETURN DISTINCT in place of the second WITH DISTINCT gives the same single row.

The rows are right when the first DISTINCT is on a non-key property, when there is only one DISTINCT, when the second clause is a plain projection or count(*), and for typed functions such as id + 1.

Are there known steps to reproduce?

import ladybug as lb

conn = lb.Connection(lb.Database(":memory:"))
conn.execute("CREATE NODE TABLE P(id INT64 PRIMARY KEY, flag BOOLEAN)")
conn.execute("CREATE (:P {id: 1, flag: true}), (:P {id: 2, flag: false}), (:P {id: 3, flag: true})")
for query in [
    "MATCH (p:P) WITH DISTINCT p.id AS id WITH DISTINCT coalesce(id, 0) AS v RETURN v",
    "MATCH (p:P) WITH DISTINCT p.id AS id, p.flag AS f WITH DISTINCT CASE WHEN f THEN id ELSE 0 END AS v RETURN v",
    "MATCH (p:P) WITH DISTINCT p.id AS id WITH DISTINCT [id] AS v RETURN v",
    "MATCH (p:P) WITH DISTINCT p.id AS id WITH DISTINCT id + 1 AS v RETURN v",
]:
    print(sorted(conn.execute(query).get_all()))

Expected: [[1], [2], [3]], [[0], [1], [3]], [[[1]], [[2]], [[3]]], [[2], [3], [4]].
Actual: [[1]], [[0], [1]], [[[1]]], [[2], [3], [4]], on both wheels.

Likely cause

RemoveUnnecessaryDistinctOptimizer drops a DISTINCT whose keys are all primary keys over a plain node scan by returning its child (remove_unnecessary_distinct_optimizer.cpp#L43). It runs after FactorizationRewriter (optimizer.cpp#L366-L367, #L376-L377; the pass says so itself at #L25-L28), so the FLATTENs placed for the removed DISTINCT stay in the plan and the operators above no longer match them; re-running factorization after the removal gives the right rows. Why only the value-copying evaluators (coalesce, CASE, list creation) go wrong under the stale flattens was not traced.