This is an automated email from the ASF dual-hosted git repository.
muhammadshoaib pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/age.git
The following commit(s) were added to refs/heads/master by this push:
new 3cc74a4f cypher_with: add ORDER BY to non-deterministic RETURN queries
(#2436)
3cc74a4f is described below
commit 3cc74a4f75252e5a9969f00bad68e3b333f01c5f
Author: John Gemignani <[email protected]>
AuthorDate: Sun Jun 21 14:34:37 2026 -0700
cypher_with: add ORDER BY to non-deterministic RETURN queries (#2436)
Several cypher_with regression queries RETURN multiple rows without an
ORDER BY, so their row order depends on heap/scan order and can vary
between runs, build types, and platforms. Add ORDER BY ASC to those
queries so the expected output is stable. Ordering keys use id() (a
single int64 that bypasses the locale-sensitive string comparison path
and is reproducible from the test's deterministic setup order), or the
projected path/scalar where that is what the query returns. Where the
underlying vertex/edge was dropped by a WITH projection, its id is
threaded through as an alias rather than reordering the projection.
Full audit of cypher_with: all 23 multi-row result blocks were checked.
After this change, every multi-row, non-EXPLAIN RETURN is deterministically
ordered. The two remaining unordered multi-row blocks are left as-is:
- "RETURN lbl" returns two identical "Person" rows, so order cannot drift;
- the 13 EXPLAIN (VERBOSE, COSTS OFF) plan blocks emit a fixed serial plan
(no parallel/gather nodes), so their row order is already deterministic.
This is a test-only change (regress/sql/cypher_with.sql and
regress/expected/cypher_with.out); no extension C code or SQL is modified.
Row counts are unchanged (pure reordering).
All 37 regression tests pass (installcheck) on PostgreSQL 18.3.
Co-authored-by: GitHub Copilot <[email protected]>
modified: regress/expected/cypher_with.out
modified: regress/sql/cypher_with.sql
---
regress/expected/cypher_with.out | 25 ++++++++++++++++++-------
regress/sql/cypher_with.sql | 13 ++++++++++++-
2 files changed, 30 insertions(+), 8 deletions(-)
diff --git a/regress/expected/cypher_with.out b/regress/expected/cypher_with.out
index 8864f026..2fc33061 100644
--- a/regress/expected/cypher_with.out
+++ b/regress/expected/cypher_with.out
@@ -52,13 +52,14 @@ SELECT * FROM cypher('cypher_with', $$
MATCH (n)-[e]->(m)
WITH n,e,m
RETURN n,e,m
+ ORDER BY id(n) ASC, id(e) ASC, id(m) ASC
$$) AS (N1 agtype, edge agtype, N2 agtype);
n1
| edge
|
n2
--------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------
{"id": 281474976710657, "label": "", "properties": {"age": 36, "name":
"Andres"}}::vertex | {"id": 844424930131969, "label": "BLOCKS", "end_id":
281474976710658, "start_id": 281474976710657, "properties": {}}::edge | {"id":
281474976710658, "label": "", "properties": {"age": 25, "name":
"Caesar"}}::vertex
- {"id": 281474976710659, "label": "", "properties": {"age": 55, "name":
"Bossman"}}::vertex | {"id": 844424930131970, "label": "BLOCKS", "end_id":
281474976710660, "start_id": 281474976710659, "properties": {}}::edge | {"id":
281474976710660, "label": "", "properties": {"age": 35, "name":
"David"}}::vertex
{"id": 281474976710657, "label": "", "properties": {"age": 36, "name":
"Andres"}}::vertex | {"id": 1125899906842625, "label": "KNOWS", "end_id":
281474976710659, "start_id": 281474976710657, "properties": {}}::edge | {"id":
281474976710659, "label": "", "properties": {"age": 55, "name":
"Bossman"}}::vertex
{"id": 281474976710658, "label": "", "properties": {"age": 25, "name":
"Caesar"}}::vertex | {"id": 1125899906842626, "label": "KNOWS", "end_id":
281474976710661, "start_id": 281474976710658, "properties": {}}::edge | {"id":
281474976710661, "label": "", "properties": {"age": 37, "name":
"George"}}::vertex
+ {"id": 281474976710659, "label": "", "properties": {"age": 55, "name":
"Bossman"}}::vertex | {"id": 844424930131970, "label": "BLOCKS", "end_id":
281474976710660, "start_id": 281474976710659, "properties": {}}::edge | {"id":
281474976710660, "label": "", "properties": {"age": 35, "name":
"David"}}::vertex
{"id": 281474976710659, "label": "", "properties": {"age": 55, "name":
"Bossman"}}::vertex | {"id": 1125899906842627, "label": "KNOWS", "end_id":
281474976710661, "start_id": 281474976710659, "properties": {}}::edge | {"id":
281474976710661, "label": "", "properties": {"age": 37, "name":
"George"}}::vertex
{"id": 281474976710660, "label": "", "properties": {"age": 35, "name":
"David"}}::vertex | {"id": 1125899906842628, "label": "KNOWS", "end_id":
281474976710657, "start_id": 281474976710660, "properties": {}}::edge | {"id":
281474976710657, "label": "", "properties": {"age": 36, "name":
"Andres"}}::vertex
(6 rows)
@@ -68,6 +69,7 @@ SELECT * FROM cypher('cypher_with', $$
MATCH (n)-[e]->(m)
WITH n.name AS n1, e as edge, m.name as n2
RETURN n1,label(edge),n2
+ ORDER BY id(edge) ASC
$$) AS (start_node agtype,edge agtype, end_node agtype);
start_node | edge | end_node
------------+----------+-----------
@@ -83,13 +85,14 @@ SELECT * FROM cypher('cypher_with',$$
MATCH (person)-[r]->(otherPerson)
WITH *, type(r) AS connectionType
RETURN person.name, connectionType, otherPerson.name
+ ORDER BY id(person) ASC, id(otherPerson) ASC
$$) AS (start_node agtype, connection agtype, end_node agtype);
start_node | connection | end_node
------------+------------+-----------
"Andres" | "BLOCKS" | "Caesar"
- "Bossman" | "BLOCKS" | "David"
"Andres" | "KNOWS" | "Bossman"
"Caesar" | "KNOWS" | "George"
+ "Bossman" | "BLOCKS" | "David"
"Bossman" | "KNOWS" | "George"
"David" | "KNOWS" | "Andres"
(6 rows)
@@ -109,6 +112,7 @@ MATCH (george {name: 'George'})<-[]-(otherPerson)
WITH otherPerson, toUpper(otherPerson.name) AS upperCaseName
WHERE upperCaseName STARTS WITH 'C'
RETURN otherPerson.name
+ ORDER BY id(otherPerson) ASC
$$) as (name agtype);
name
----------
@@ -120,6 +124,7 @@ SELECT * FROM cypher('cypher_with', $$
WITH otherPerson, count(*) AS foaf
WHERE foaf > 1
RETURN otherPerson.name
+ ORDER BY id(otherPerson) ASC
$$) as (name agtype);
name
----------
@@ -131,15 +136,16 @@ SELECT * FROM cypher('cypher_with', $$
WITH p, length(p) AS path_length
WHERE path_length > 1
RETURN p
+ ORDER BY p ASC
$$) AS (pattern agtype);
pattern
[...]
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[...]
- [{"id": 281474976710657, "label": "_ag_label_vertex", "properties": {"age":
36, "name": "Andres"}}::vertex, {"id": 1125899906842625, "label": "KNOWS",
"end_id": 281474976710659, "start_id": 281474976710657, "properties":
{}}::edge, {"id": 281474976710659, "label": "_ag_label_vertex", "properties":
{"age": 55, "name": "Bossman"}}::vertex, {"id": 1125899906842627, "label":
"KNOWS", "end_id": 281474976710661, "start_id": 281474976710659, "properties":
{}}::edge, {"id": 281474976710661, "la [...]
- [{"id": 281474976710657, "label": "_ag_label_vertex", "properties": {"age":
36, "name": "Andres"}}::vertex, {"id": 1125899906842625, "label": "KNOWS",
"end_id": 281474976710659, "start_id": 281474976710657, "properties":
{}}::edge, {"id": 281474976710659, "label": "_ag_label_vertex", "properties":
{"age": 55, "name": "Bossman"}}::vertex, {"id": 844424930131970, "label":
"BLOCKS", "end_id": 281474976710660, "start_id": 281474976710659, "properties":
{}}::edge, {"id": 281474976710660, "la [...]
[{"id": 281474976710657, "label": "_ag_label_vertex", "properties": {"age":
36, "name": "Andres"}}::vertex, {"id": 844424930131969, "label": "BLOCKS",
"end_id": 281474976710658, "start_id": 281474976710657, "properties":
{}}::edge, {"id": 281474976710658, "label": "_ag_label_vertex", "properties":
{"age": 25, "name": "Caesar"}}::vertex, {"id": 1125899906842626, "label":
"KNOWS", "end_id": 281474976710661, "start_id": 281474976710658, "properties":
{}}::edge, {"id": 281474976710661, "lab [...]
+ [{"id": 281474976710657, "label": "_ag_label_vertex", "properties": {"age":
36, "name": "Andres"}}::vertex, {"id": 1125899906842625, "label": "KNOWS",
"end_id": 281474976710659, "start_id": 281474976710657, "properties":
{}}::edge, {"id": 281474976710659, "label": "_ag_label_vertex", "properties":
{"age": 55, "name": "Bossman"}}::vertex, {"id": 844424930131970, "label":
"BLOCKS", "end_id": 281474976710660, "start_id": 281474976710659, "properties":
{}}::edge, {"id": 281474976710660, "la [...]
+ [{"id": 281474976710657, "label": "_ag_label_vertex", "properties": {"age":
36, "name": "Andres"}}::vertex, {"id": 1125899906842625, "label": "KNOWS",
"end_id": 281474976710659, "start_id": 281474976710657, "properties":
{}}::edge, {"id": 281474976710659, "label": "_ag_label_vertex", "properties":
{"age": 55, "name": "Bossman"}}::vertex, {"id": 1125899906842627, "label":
"KNOWS", "end_id": 281474976710661, "start_id": 281474976710659, "properties":
{}}::edge, {"id": 281474976710661, "la [...]
[{"id": 281474976710659, "label": "_ag_label_vertex", "properties": {"age":
55, "name": "Bossman"}}::vertex, {"id": 844424930131970, "label": "BLOCKS",
"end_id": 281474976710660, "start_id": 281474976710659, "properties":
{}}::edge, {"id": 281474976710660, "label": "_ag_label_vertex", "properties":
{"age": 35, "name": "David"}}::vertex, {"id": 1125899906842628, "label":
"KNOWS", "end_id": 281474976710657, "start_id": 281474976710660, "properties":
{}}::edge, {"id": 281474976710657, "lab [...]
- [{"id": 281474976710660, "label": "_ag_label_vertex", "properties": {"age":
35, "name": "David"}}::vertex, {"id": 1125899906842628, "label": "KNOWS",
"end_id": 281474976710657, "start_id": 281474976710660, "properties":
{}}::edge, {"id": 281474976710657, "label": "_ag_label_vertex", "properties":
{"age": 36, "name": "Andres"}}::vertex, {"id": 1125899906842625, "label":
"KNOWS", "end_id": 281474976710659, "start_id": 281474976710657, "properties":
{}}::edge, {"id": 281474976710659, "labe [...]
[{"id": 281474976710660, "label": "_ag_label_vertex", "properties": {"age":
35, "name": "David"}}::vertex, {"id": 1125899906842628, "label": "KNOWS",
"end_id": 281474976710657, "start_id": 281474976710660, "properties":
{}}::edge, {"id": 281474976710657, "label": "_ag_label_vertex", "properties":
{"age": 36, "name": "Andres"}}::vertex, {"id": 844424930131969, "label":
"BLOCKS", "end_id": 281474976710658, "start_id": 281474976710657, "properties":
{}}::edge, {"id": 281474976710658, "labe [...]
+ [{"id": 281474976710660, "label": "_ag_label_vertex", "properties": {"age":
35, "name": "David"}}::vertex, {"id": 1125899906842628, "label": "KNOWS",
"end_id": 281474976710657, "start_id": 281474976710660, "properties":
{}}::edge, {"id": 281474976710657, "label": "_ag_label_vertex", "properties":
{"age": 36, "name": "Andres"}}::vertex, {"id": 1125899906842625, "label":
"KNOWS", "end_id": 281474976710659, "start_id": 281474976710657, "properties":
{}}::edge, {"id": 281474976710659, "labe [...]
(6 rows)
-- MATCH/WHERE with WITH/WHERE
@@ -149,6 +155,7 @@ SELECT * FROM cypher('cypher_with', $$
WITH *
WHERE m.name = 'Andres'
RETURN m.name,label(e),b.name
+ ORDER BY id(m) ASC, id(e) ASC, id(b) ASC
$$) AS (N1 agtype, edge agtype, N2 agtype);
n1 | edge | n2
----------+---------+-----------
@@ -201,9 +208,10 @@ SELECT * FROM cypher('cypher_with', $$
MATCH (n)-[e]->(m)
WITH n, e, m
WHERE label(e) = 'KNOWS'
- WITH n.name as n1, label(e) as edge, m.name as n2
+ WITH id(e) AS eid, n.name as n1, label(e) as edge, m.name as n2
WHERE n1 = 'Andres'
RETURN n1,edge,n2
+ ORDER BY eid ASC
$$) AS (N1 agtype, edge agtype, N2 agtype);
n1 | edge | n2
----------+---------+-----------
@@ -217,6 +225,7 @@ SELECT * FROM cypher('cypher_with', $$
WITH x
LIMIT 5
RETURN x
+ ORDER BY x ASC
$$) as (name agtype);
name
------
@@ -233,11 +242,12 @@ SELECT * FROM cypher('cypher_with', $$
WITH m as start_node, b as end_node
WHERE end_node.name = 'George'
RETURN id(start_node),start_node.name,id(end_node),end_node.name
+ ORDER BY id(start_node) ASC, id(end_node) ASC
$$) AS (id1 agtype, name1 agtype, id2 agtype, name2 agtype);
id1 | name1 | id2 | name2
-----------------+-----------+-----------------+----------
- 281474976710659 | "Bossman" | 281474976710661 | "George"
281474976710658 | "Caesar" | 281474976710661 | "George"
+ 281474976710659 | "Bossman" | 281474976710661 | "George"
(2 rows)
-- Expression item must be aliased.
@@ -471,6 +481,7 @@ SELECT * FROM cypher('with_accessor_opt', $$
MATCH (n:Person)
WITH n as m
RETURN m
+ ORDER BY id(m) ASC
$$) AS (n vertex);
n
---------------------------------------------------------------------
diff --git a/regress/sql/cypher_with.sql b/regress/sql/cypher_with.sql
index 25e22b2a..14535644 100644
--- a/regress/sql/cypher_with.sql
+++ b/regress/sql/cypher_with.sql
@@ -47,6 +47,7 @@ SELECT * FROM cypher('cypher_with', $$
MATCH (n)-[e]->(m)
WITH n,e,m
RETURN n,e,m
+ ORDER BY id(n) ASC, id(e) ASC, id(m) ASC
$$) AS (N1 agtype, edge agtype, N2 agtype);
-- WITH/AS
@@ -55,12 +56,14 @@ SELECT * FROM cypher('cypher_with', $$
MATCH (n)-[e]->(m)
WITH n.name AS n1, e as edge, m.name as n2
RETURN n1,label(edge),n2
+ ORDER BY id(edge) ASC
$$) AS (start_node agtype,edge agtype, end_node agtype);
SELECT * FROM cypher('cypher_with',$$
MATCH (person)-[r]->(otherPerson)
WITH *, type(r) AS connectionType
RETURN person.name, connectionType, otherPerson.name
+ ORDER BY id(person) ASC, id(otherPerson) ASC
$$) AS (start_node agtype, connection agtype, end_node agtype);
SELECT * FROM cypher('cypher_with', $$
@@ -75,6 +78,7 @@ MATCH (george {name: 'George'})<-[]-(otherPerson)
WITH otherPerson, toUpper(otherPerson.name) AS upperCaseName
WHERE upperCaseName STARTS WITH 'C'
RETURN otherPerson.name
+ ORDER BY id(otherPerson) ASC
$$) as (name agtype);
SELECT * FROM cypher('cypher_with', $$
@@ -82,6 +86,7 @@ SELECT * FROM cypher('cypher_with', $$
WITH otherPerson, count(*) AS foaf
WHERE foaf > 1
RETURN otherPerson.name
+ ORDER BY id(otherPerson) ASC
$$) as (name agtype);
SELECT * FROM cypher('cypher_with', $$
@@ -89,6 +94,7 @@ SELECT * FROM cypher('cypher_with', $$
WITH p, length(p) AS path_length
WHERE path_length > 1
RETURN p
+ ORDER BY p ASC
$$) AS (pattern agtype);
-- MATCH/WHERE with WITH/WHERE
@@ -99,6 +105,7 @@ SELECT * FROM cypher('cypher_with', $$
WITH *
WHERE m.name = 'Andres'
RETURN m.name,label(e),b.name
+ ORDER BY id(m) ASC, id(e) ASC, id(b) ASC
$$) AS (N1 agtype, edge agtype, N2 agtype);
-- WITH/ORDER BY
@@ -133,9 +140,10 @@ SELECT * FROM cypher('cypher_with', $$
MATCH (n)-[e]->(m)
WITH n, e, m
WHERE label(e) = 'KNOWS'
- WITH n.name as n1, label(e) as edge, m.name as n2
+ WITH id(e) AS eid, n.name as n1, label(e) as edge, m.name as n2
WHERE n1 = 'Andres'
RETURN n1,edge,n2
+ ORDER BY eid ASC
$$) AS (N1 agtype, edge agtype, N2 agtype);
SELECT * FROM cypher('cypher_with', $$
@@ -145,6 +153,7 @@ SELECT * FROM cypher('cypher_with', $$
WITH x
LIMIT 5
RETURN x
+ ORDER BY x ASC
$$) as (name agtype);
SELECT * FROM cypher('cypher_with', $$
@@ -154,6 +163,7 @@ SELECT * FROM cypher('cypher_with', $$
WITH m as start_node, b as end_node
WHERE end_node.name = 'George'
RETURN id(start_node),start_node.name,id(end_node),end_node.name
+ ORDER BY id(start_node) ASC, id(end_node) ASC
$$) AS (id1 agtype, name1 agtype, id2 agtype, name2 agtype);
-- Expression item must be aliased.
@@ -284,6 +294,7 @@ SELECT * FROM cypher('with_accessor_opt', $$
MATCH (n:Person)
WITH n as m
RETURN m
+ ORDER BY id(m) ASC
$$) AS (n vertex);
SELECT * FROM cypher('with_accessor_opt', $$
