You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@age.apache.org by "crprashant (via GitHub)" <gi...@apache.org> on 2026/04/23 01:04:45 UTC

[PR] Fix UNWIND [null] causing count() to miscount null rows (#2383) [age]

crprashant opened a new pull request, #2411:
URL: https://github.com/apache/age/pull/2411

   Fixes #2383.
   
   ## Problem
   
   Per Neo4j / openCypher semantics, `count()` must ignore nulls. On Apache AGE:
   
   ```cypher
   UNWIND [null] AS x RETURN count(x)   -- returns 1 (buggy), should be 0
   ```
   
   The control cases already returned the correct `0`:
   
   ```cypher
   RETURN count(null)
   WITH null AS x RETURN count(x)
   ```
   
   ## Root cause
   
   `age_unnest()` in `src/backend/utils/adt/agtype.c` materialized every top-level array element into a heap tuple with `nulls[0] = false`, even when the element's `agtype_value.type == AGTV_NULL`. The row that reached `count()` therefore carried a non-NULL datum wrapping an agtype JSON null, so the aggregate's strict null check never skipped it.
   
   ## Fix
   
   A naïve fix inside `age_unnest()` breaks other callers. `construct_age_function_name()` in `src/backend/parser/cypher_expr.c` rewrites `unnest` → `age_unnest`, so list comprehensions and the predicate functions (`all` / `any` / `none` / `single`) also route through `age_unnest`. Those callers deliberately rely on the quirk that AGE's `agtype_null > agtype_integer` evaluates to `agtype_null` (truthy), not SQL NULL. Changing the SRF broke five `predicate_functions` expected results.
   
   Introduce a dedicated SRF `age_unwind()` with UNWIND-specific null semantics:
   
   - Top-level `AGTV_NULL` elements → SQL NULL rows so null-strict operators (`count`, `IS NULL`, `WHERE x IS NOT NULL`, …) match `WITH null AS x`.
   - Non-null elements are returned unchanged.
   - Nested nulls inside arrays/objects are preserved as agtype-null.
   
   `transform_cypher_unwind()` is the only call site changed from `age_unnest` to `age_unwind`. List comprehensions and predicate functions continue to use `age_unnest` and their behavior is untouched.
   
   ## Before / after
   
   ```text
   -- before
   UNWIND [null] AS x RETURN count(x)                  -- 1
   UNWIND [1, null, 2, null, 3] AS x RETURN count(x)   -- 5
   UNWIND [null] AS x RETURN x IS NULL                 -- false
   
   -- after
   UNWIND [null] AS x RETURN count(x)                  -- 0 ✓
   UNWIND [1, null, 2, null, 3] AS x RETURN count(x)   -- 3 ✓
   UNWIND [null] AS x RETURN x IS NULL                 -- true ✓
   ```
   
   ## Regression tests (`regress/sql/cypher_unwind.sql`)
   
   - `UNWIND [null] RETURN count(x) = 0`
   - `UNWIND [1, null, 2, null, 3] RETURN count(x) = 3`
   - `UNWIND [null] RETURN x IS NULL`
   - `UNWIND [1, null, 2] WITH x WHERE x IS NOT NULL RETURN count(x) = 2`
   - `UNWIND [null] RETURN count(*) = 1` (row still produced)
   - `UNWIND [[null], {k: null}] RETURN count(x) = 2` (nested nulls preserved)
   - `UNWIND [null] RETURN x` — asserts the row value itself is SQL NULL
   
   ## Files changed
   
   - `src/backend/utils/adt/agtype.c` — new `age_unwind` SRF
   - `src/backend/parser/cypher_clause.c` — UNWIND calls `age_unwind`
   - `sql/agtype_typecast.sql` — declare `age_unwind`
   - `age--1.7.0--y.y.y.sql` — add `age_unwind` for in-place upgrades
   - `regress/sql/cypher_unwind.sql` + `regress/expected/cypher_unwind.out`
   
   ## Test results
   
   `make installcheck` with PostgreSQL 18 on Ubuntu 24.04:
   
   - **32/33 tests pass.**
   - `age_upgrade` fails identically on `apache/age` master `HEAD` (pre-existing, unrelated; same `age_prepare_pg_upgrade already exists` error).
   - `cypher_unwind`, `predicate_functions`, `list_comprehension` all pass.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@age.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Fix UNWIND [null] causing count() to miscount null rows (#2383) [age]

Posted by "crprashant (via GitHub)" <gi...@apache.org>.
crprashant commented on PR #2411:
URL: https://github.com/apache/age/pull/2411#issuecomment-4301226842

   Heads-up for reviewers — just noticed @MuhammadTahaNaveed's #2406 fixes the same root cause (agtype `AGTV_NULL` not being emitted as SQL NULL from `age_unnest`) with a broader scope that also covers `single()` / `all()` / `any()` / `none()` three-valued semantics. That direction was my first instinct too, but I backed off after it altered the `predicate_functions` regression; #2406 goes further and reworks those predicates to match Neo4j three-valued logic, which is the right call. #2406 subsumes this PR.
   
   Happy to close #2411 in favor of #2406 once it lands. The 7 regression cases in `regress/sql/cypher_unwind.sql` here specifically cover `UNWIND [null] AS x RETURN count(x) = 0` (issue #2383) and a few `UNWIND`-only boundaries (nested lists, mixed nulls, `DISTINCT` over `UNWIND`) that #2406's tests don't exercise — glad to open a small follow-up PR porting just those tests on top of #2406 if that would be useful.
   
   Thanks for the fast work on the null-propagation family of bugs.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@age.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Fix UNWIND [null] causing count() to miscount null rows (#2383) [age]

Posted by "crprashant (via GitHub)" <gi...@apache.org>.
crprashant commented on code in PR #2411:
URL: https://github.com/apache/age/pull/2411#discussion_r3171351024


##########
src/backend/utils/adt/agtype.c:
##########
@@ -12450,6 +12450,115 @@ Datum age_unnest(PG_FUNCTION_ARGS)
     PG_RETURN_NULL();
 }
 
+PG_FUNCTION_INFO_V1(age_unwind);
+/*
+ * Function to convert an agtype array into a set of rows for the Cypher
+ * `UNWIND` clause. Behaves like age_unnest() but, per Neo4j/openCypher
+ * semantics, emits an SQL NULL row for each top-level element that is an
+ * agtype JSON null (AGTV_NULL). This lets null-strict operators such as
+ * count(x) and IS NULL treat `UNWIND [null] AS x` the same as
+ * `WITH null AS x`. Nested nulls inside arrays/objects are preserved as
+ * agtype-null so container semantics are unchanged. See issue #2383.
+ */
+Datum age_unwind(PG_FUNCTION_ARGS)
+{
+    agtype *agtype_arg = NULL;
+    ReturnSetInfo *rsi;
+    Tuplestorestate *tuple_store;
+    TupleDesc tupdesc;
+    TupleDesc ret_tdesc;
+    MemoryContext old_cxt, tmp_cxt;
+    bool skipNested = false;
+    agtype_iterator *it;
+    agtype_value v;
+    agtype_iterator_token r;
+
+    /* check for a NULL expr */
+    if (PG_ARGISNULL(0))
+    {
+        PG_RETURN_NULL();
+    }
+
+    agtype_arg = AG_GET_ARG_AGTYPE_P(0);
+    if (!AGT_ROOT_IS_ARRAY(agtype_arg))
+    {
+        ereport(ERROR,
+                (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+                        errmsg("cannot extract elements from an object")));

Review Comment:
   Good catch — fixed in 50b42533. Updated the message in age_unwind to "cannot extract elements from a non-array". Note: age_unnest (pre-existing in this file) has the identical wording but I left it untouched to keep this PR scoped to #2383; happy to file a follow-up.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@age.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Fix UNWIND [null] causing count() to miscount null rows (#2383) [age]

Posted by "gregfelice (via GitHub)" <gi...@apache.org>.
gregfelice commented on PR #2411:
URL: https://github.com/apache/age/pull/2411#issuecomment-4875620695

   Thanks for this, @crprashant. We ended up landing #2406 for #2383, which fixes the root cause in `age_unnest` itself and so covers `UNWIND [null]` **plus** the related list-comprehension null-filter (#2393) and `single()` three-valued logic in one change. Since that supersedes the UNWIND-specific fix here, I'm closing this PR — but the analysis in it was genuinely helpful in scoping the fix, so thank you.
   
   If you'd like to keep contributing in this area, #2416 (keyword-named aliases) is still open and yours — happy to review once it's ready.
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@age.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Fix UNWIND [null] causing count() to miscount null rows (#2383) [age]

Posted by "gregfelice (via GitHub)" <gi...@apache.org>.
gregfelice closed pull request #2411: Fix UNWIND [null] causing count() to miscount null rows (#2383)
URL: https://github.com/apache/age/pull/2411


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@age.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Fix UNWIND [null] causing count() to miscount null rows (#2383) [age]

Posted by "Copilot (via GitHub)" <gi...@apache.org>.
Copilot commented on code in PR #2411:
URL: https://github.com/apache/age/pull/2411#discussion_r3164572475


##########
src/backend/utils/adt/agtype.c:
##########
@@ -12450,6 +12450,115 @@ Datum age_unnest(PG_FUNCTION_ARGS)
     PG_RETURN_NULL();
 }
 
+PG_FUNCTION_INFO_V1(age_unwind);
+/*
+ * Function to convert an agtype array into a set of rows for the Cypher
+ * `UNWIND` clause. Behaves like age_unnest() but, per Neo4j/openCypher
+ * semantics, emits an SQL NULL row for each top-level element that is an
+ * agtype JSON null (AGTV_NULL). This lets null-strict operators such as
+ * count(x) and IS NULL treat `UNWIND [null] AS x` the same as
+ * `WITH null AS x`. Nested nulls inside arrays/objects are preserved as
+ * agtype-null so container semantics are unchanged. See issue #2383.
+ */
+Datum age_unwind(PG_FUNCTION_ARGS)
+{
+    agtype *agtype_arg = NULL;
+    ReturnSetInfo *rsi;
+    Tuplestorestate *tuple_store;
+    TupleDesc tupdesc;
+    TupleDesc ret_tdesc;
+    MemoryContext old_cxt, tmp_cxt;
+    bool skipNested = false;
+    agtype_iterator *it;
+    agtype_value v;
+    agtype_iterator_token r;
+
+    /* check for a NULL expr */
+    if (PG_ARGISNULL(0))
+    {
+        PG_RETURN_NULL();
+    }
+
+    agtype_arg = AG_GET_ARG_AGTYPE_P(0);
+    if (!AGT_ROOT_IS_ARRAY(agtype_arg))
+    {
+        ereport(ERROR,
+                (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+                        errmsg("cannot extract elements from an object")));
+    }
+
+    rsi = (ReturnSetInfo *) fcinfo->resultinfo;
+
+    rsi->returnMode = SFRM_Materialize;
+
+    /* it's a simple type, so don't use get_call_result_type() */
+    tupdesc = rsi->expectedDesc;
+
+    old_cxt = MemoryContextSwitchTo(rsi->econtext->ecxt_per_query_memory);
+
+    ret_tdesc = CreateTupleDescCopy(tupdesc);
+    BlessTupleDesc(ret_tdesc);
+    tuple_store =
+            tuplestore_begin_heap(rsi->allowedModes & SFRM_Materialize_Random,
+                                  false, work_mem);
+
+    MemoryContextSwitchTo(old_cxt);
+
+    tmp_cxt = AllocSetContextCreate(CurrentMemoryContext,
+                                    "age_unwind temporary cxt",
+                                    ALLOCSET_DEFAULT_SIZES);
+
+    it = agtype_iterator_init(&agtype_arg->root);
+
+    while ((r = agtype_iterator_next(&it, &v, skipNested)) != WAGT_DONE)
+    {
+        skipNested = true;
+
+        if (r == WAGT_ELEM)
+        {
+            HeapTuple tuple;
+            Datum values[1] = {(Datum) 0};
+            bool nulls[1] = {false};
+
+            /* use the tmp context so we can clean up after each tuple is done */
+            old_cxt = MemoryContextSwitchTo(tmp_cxt);
+
+            if (v.type == AGTV_NULL)
+            {
+                /* emit SQL NULL for agtype JSON null (issue #2383) */
+                nulls[0] = true;
+            }
+            else
+            {
+                agtype *val = agtype_value_to_agtype(&v);
+
+                values[0] = PointerGetDatum(val);
+            }
+
+            tuple = heap_form_tuple(ret_tdesc, values, nulls);
+
+            /* switch to the per query context to hold the tuple */
+            MemoryContextSwitchTo(rsi->econtext->ecxt_per_query_memory);
+
+            tuplestore_puttuple(tuple_store, tuple);
+
+            /* now reset the tmp context */
+            MemoryContextSwitchTo(tmp_cxt);
+            MemoryContextReset(tmp_cxt);
+
+            /* reset back to the old context */
+            MemoryContextSwitchTo(old_cxt);

Review Comment:
   Inside the per-element loop, `age_unwind` performs extra `MemoryContextSwitchTo` calls (switching to per-query memory before `tuplestore_puttuple`, then back to `tmp_cxt`, then back again). `age_unnest` simply calls `tuplestore_puttuple` directly and resets the temp context, which is both simpler and avoids additional context switches on large lists. Consider matching `age_unnest`'s pattern here to reduce overhead and keep the two SRFs structurally aligned.
   ```suggestion
               tuplestore_puttuple(tuple_store, tuple);
   
               /* reset back to the old context, then clear tmp allocations */
               MemoryContextSwitchTo(old_cxt);
               MemoryContextReset(tmp_cxt);
   ```



##########
src/backend/utils/adt/agtype.c:
##########
@@ -12450,6 +12450,115 @@ Datum age_unnest(PG_FUNCTION_ARGS)
     PG_RETURN_NULL();
 }
 
+PG_FUNCTION_INFO_V1(age_unwind);
+/*
+ * Function to convert an agtype array into a set of rows for the Cypher
+ * `UNWIND` clause. Behaves like age_unnest() but, per Neo4j/openCypher
+ * semantics, emits an SQL NULL row for each top-level element that is an
+ * agtype JSON null (AGTV_NULL). This lets null-strict operators such as
+ * count(x) and IS NULL treat `UNWIND [null] AS x` the same as
+ * `WITH null AS x`. Nested nulls inside arrays/objects are preserved as
+ * agtype-null so container semantics are unchanged. See issue #2383.
+ */
+Datum age_unwind(PG_FUNCTION_ARGS)
+{
+    agtype *agtype_arg = NULL;
+    ReturnSetInfo *rsi;
+    Tuplestorestate *tuple_store;
+    TupleDesc tupdesc;
+    TupleDesc ret_tdesc;
+    MemoryContext old_cxt, tmp_cxt;
+    bool skipNested = false;
+    agtype_iterator *it;
+    agtype_value v;
+    agtype_iterator_token r;
+
+    /* check for a NULL expr */
+    if (PG_ARGISNULL(0))
+    {
+        PG_RETURN_NULL();
+    }
+
+    agtype_arg = AG_GET_ARG_AGTYPE_P(0);
+    if (!AGT_ROOT_IS_ARRAY(agtype_arg))
+    {
+        ereport(ERROR,
+                (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+                        errmsg("cannot extract elements from an object")));

Review Comment:
   The error raised when the input is not an array uses `errmsg("cannot extract elements from an object")`, but this branch is also hit for agtype scalars (and potentially other non-array roots). Consider updating the message to something type-accurate like "cannot extract elements from a non-array" (or explicitly handle scalar/object separately) so UNWIND errors are not misleading.
   ```suggestion
                           errmsg("cannot extract elements from a non-array")));
   ```



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@age.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Fix UNWIND [null] causing count() to miscount null rows (#2383) [age]

Posted by "crprashant (via GitHub)" <gi...@apache.org>.
crprashant commented on code in PR #2411:
URL: https://github.com/apache/age/pull/2411#discussion_r3171358103


##########
src/backend/utils/adt/agtype.c:
##########
@@ -12450,6 +12450,115 @@ Datum age_unnest(PG_FUNCTION_ARGS)
     PG_RETURN_NULL();
 }
 
+PG_FUNCTION_INFO_V1(age_unwind);
+/*
+ * Function to convert an agtype array into a set of rows for the Cypher
+ * `UNWIND` clause. Behaves like age_unnest() but, per Neo4j/openCypher
+ * semantics, emits an SQL NULL row for each top-level element that is an
+ * agtype JSON null (AGTV_NULL). This lets null-strict operators such as
+ * count(x) and IS NULL treat `UNWIND [null] AS x` the same as
+ * `WITH null AS x`. Nested nulls inside arrays/objects are preserved as
+ * agtype-null so container semantics are unchanged. See issue #2383.
+ */
+Datum age_unwind(PG_FUNCTION_ARGS)
+{
+    agtype *agtype_arg = NULL;
+    ReturnSetInfo *rsi;
+    Tuplestorestate *tuple_store;
+    TupleDesc tupdesc;
+    TupleDesc ret_tdesc;
+    MemoryContext old_cxt, tmp_cxt;
+    bool skipNested = false;
+    agtype_iterator *it;
+    agtype_value v;
+    agtype_iterator_token r;
+
+    /* check for a NULL expr */
+    if (PG_ARGISNULL(0))
+    {
+        PG_RETURN_NULL();
+    }
+
+    agtype_arg = AG_GET_ARG_AGTYPE_P(0);
+    if (!AGT_ROOT_IS_ARRAY(agtype_arg))
+    {
+        ereport(ERROR,
+                (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+                        errmsg("cannot extract elements from an object")));
+    }
+
+    rsi = (ReturnSetInfo *) fcinfo->resultinfo;
+
+    rsi->returnMode = SFRM_Materialize;
+
+    /* it's a simple type, so don't use get_call_result_type() */
+    tupdesc = rsi->expectedDesc;
+
+    old_cxt = MemoryContextSwitchTo(rsi->econtext->ecxt_per_query_memory);
+
+    ret_tdesc = CreateTupleDescCopy(tupdesc);
+    BlessTupleDesc(ret_tdesc);
+    tuple_store =
+            tuplestore_begin_heap(rsi->allowedModes & SFRM_Materialize_Random,
+                                  false, work_mem);
+
+    MemoryContextSwitchTo(old_cxt);
+
+    tmp_cxt = AllocSetContextCreate(CurrentMemoryContext,
+                                    "age_unwind temporary cxt",
+                                    ALLOCSET_DEFAULT_SIZES);
+
+    it = agtype_iterator_init(&agtype_arg->root);
+
+    while ((r = agtype_iterator_next(&it, &v, skipNested)) != WAGT_DONE)
+    {
+        skipNested = true;
+
+        if (r == WAGT_ELEM)
+        {
+            HeapTuple tuple;
+            Datum values[1] = {(Datum) 0};
+            bool nulls[1] = {false};
+
+            /* use the tmp context so we can clean up after each tuple is done */
+            old_cxt = MemoryContextSwitchTo(tmp_cxt);
+
+            if (v.type == AGTV_NULL)
+            {
+                /* emit SQL NULL for agtype JSON null (issue #2383) */
+                nulls[0] = true;
+            }
+            else
+            {
+                agtype *val = agtype_value_to_agtype(&v);
+
+                values[0] = PointerGetDatum(val);
+            }
+
+            tuple = heap_form_tuple(ret_tdesc, values, nulls);
+
+            /* switch to the per query context to hold the tuple */
+            MemoryContextSwitchTo(rsi->econtext->ecxt_per_query_memory);
+
+            tuplestore_puttuple(tuple_store, tuple);
+
+            /* now reset the tmp context */
+            MemoryContextSwitchTo(tmp_cxt);
+            MemoryContextReset(tmp_cxt);
+
+            /* reset back to the old context */
+            MemoryContextSwitchTo(old_cxt);

Review Comment:
   Agreed — fixed in 50b42533. Removed the redundant per-element MemoryContextSwitchTo dance and now mirror age_unnest exactly: switch to tmp_cxt, build the tuple, tuplestore_puttuple (which copies into the tuplestores own context), then switch back and reset tmp_cxt. Net -9/+3 lines.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@age.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org