This is an automated email from the ASF dual-hosted git repository.
mtaha 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 898481a8 Fix ORDER BY alias resolution with AS in Cypher queries
(#2269)
898481a8 is described below
commit 898481a8c79381cc9f49ca71a9756a6216b61353
Author: John Gemignani <[email protected]>
AuthorDate: Wed Dec 10 09:08:36 2025 -0800
Fix ORDER BY alias resolution with AS in Cypher queries (#2269)
NOTE: This PR was partially created with AI tools and reviewed by a human.
ORDER BY clauses failed when referencing column aliases from RETURN:
MATCH (p:Person) RETURN p.age AS age ORDER BY age DESC
ERROR: could not find rte for age
Added SQL-99 compliant alias matching to find_target_list_entry() that
checks if ORDER BY identifier matches a target list alias before
attempting expression transformation. This enables standard SQL behavior
for sorting by aliased columns with DESC/DESCENDING/ASC/ASCENDING.
Updated regression tests.
Added regression tests.
modified: regress/expected/cypher_match.out
modified: regress/expected/expr.out
modified: regress/sql/expr.sql
modified: src/backend/parser/cypher_clause.c
---
regress/expected/cypher_match.out | 24 +++++------
regress/expected/expr.out | 88 ++++++++++++++++++++++++++++++++++++++
regress/sql/expr.sql | 30 +++++++++++++
src/backend/parser/cypher_clause.c | 26 +++++++++++
4 files changed, 156 insertions(+), 12 deletions(-)
diff --git a/regress/expected/cypher_match.out
b/regress/expected/cypher_match.out
index 72ca1cd7..a0e284be 100644
--- a/regress/expected/cypher_match.out
+++ b/regress/expected/cypher_match.out
@@ -1655,10 +1655,10 @@ SELECT * FROM cypher('cypher_match', $$
$$) AS (u agtype, m agtype, l agtype);
u | m | l
------------+---------------+------------
- "someone" | "opt_match_e" | "somebody"
- "somebody" | "opt_match_e" | "someone"
"anybody" | "opt_match_e" | "nobody"
"nobody" | "opt_match_e" | "anybody"
+ "somebody" | "opt_match_e" | "someone"
+ "someone" | "opt_match_e" | "somebody"
(4 rows)
SELECT * FROM cypher('cypher_match', $$
@@ -1670,8 +1670,8 @@ SELECT * FROM cypher('cypher_match', $$
$$) AS (n agtype, r agtype, p agtype, m agtype, s agtype, q agtype);
n | r | p | m | s | q
-----------+---------------+------------+-----------+---------------+------------
- "someone" | "opt_match_e" | "somebody" | "anybody" | "opt_match_e" | "nobody"
"anybody" | "opt_match_e" | "nobody" | "someone" | "opt_match_e" |
"somebody"
+ "someone" | "opt_match_e" | "somebody" | "anybody" | "opt_match_e" | "nobody"
(2 rows)
SELECT * FROM cypher('cypher_match', $$
@@ -1684,18 +1684,18 @@ SELECT * FROM cypher('cypher_match', $$
$$) AS (n agtype, r agtype, p agtype, m agtype, s agtype, q agtype);
n | r | p | m | s | q
------------+---------------+------------+------------+---------------+------------
- "someone" | "opt_match_e" | "somebody" | "anybody" | "opt_match_e" |
"nobody"
- "someone" | | | "somebody" | |
- "someone" | | | "nobody" | |
- "somebody" | | | "someone" | |
- "somebody" | | | "anybody" | |
- "somebody" | | | "nobody" | |
"anybody" | "opt_match_e" | "nobody" | "someone" | "opt_match_e" |
"somebody"
- "anybody" | | | "somebody" | |
"anybody" | | | "nobody" | |
- "nobody" | | | "someone" | |
- "nobody" | | | "somebody" | |
+ "anybody" | | | "somebody" | |
"nobody" | | | "anybody" | |
+ "nobody" | | | "somebody" | |
+ "nobody" | | | "someone" | |
+ "somebody" | | | "anybody" | |
+ "somebody" | | | "nobody" | |
+ "somebody" | | | "someone" | |
+ "someone" | "opt_match_e" | "somebody" | "anybody" | "opt_match_e" |
"nobody"
+ "someone" | | | "nobody" | |
+ "someone" | | | "somebody" | |
(12 rows)
-- Tests to catch match following optional match logic
diff --git a/regress/expected/expr.out b/regress/expected/expr.out
index 4be3bf90..926a958d 100644
--- a/regress/expected/expr.out
+++ b/regress/expected/expr.out
@@ -6949,6 +6949,94 @@ $$) AS (i agtype);
{"key": "value"}
(9 rows)
+--
+-- Test ORDER BY with AS
+--
+SELECT * FROM cypher('order_by', $$ CREATE ({name: 'John', age: 38}) $$) AS
(result agtype);
+ result
+--------
+(0 rows)
+
+SELECT * FROM cypher('order_by', $$ CREATE ({name: 'Jill', age: 23}) $$) AS
(result agtype);
+ result
+--------
+(0 rows)
+
+SELECT * FROM cypher('order_by', $$ CREATE ({name: 'Ion', age: 34}) $$) AS
(result agtype);
+ result
+--------
+(0 rows)
+
+SELECT * FROM cypher('order_by', $$ CREATE ({name: 'Mary', age: 57}) $$) AS
(result agtype);
+ result
+--------
+(0 rows)
+
+SELECT * FROM cypher('order_by', $$ CREATE ({name: 'Jerry', age: 34}) $$) AS
(result agtype);
+ result
+--------
+(0 rows)
+
+SELECT * FROM cypher('order_by', $$
+ MATCH (u) WHERE EXISTS(u.name) RETURN u.name AS name, u.age AS age
ORDER BY name
+$$) AS (name agtype, age agtype);
+ name | age
+---------+-----
+ "Ion" | 34
+ "Jerry" | 34
+ "Jill" | 23
+ "John" | 38
+ "Mary" | 57
+(5 rows)
+
+SELECT * FROM cypher('order_by', $$
+ MATCH (u) WHERE EXISTS(u.name) RETURN u.name AS name, u.age AS age
ORDER BY name ASC
+$$) AS (name agtype, age agtype);
+ name | age
+---------+-----
+ "Ion" | 34
+ "Jerry" | 34
+ "Jill" | 23
+ "John" | 38
+ "Mary" | 57
+(5 rows)
+
+SELECT * FROM cypher('order_by', $$
+ MATCH (u) WHERE EXISTS(u.name) RETURN u.name AS name, u.age AS age
ORDER BY name DESC
+$$) AS (name agtype, age agtype);
+ name | age
+---------+-----
+ "Mary" | 57
+ "John" | 38
+ "Jill" | 23
+ "Jerry" | 34
+ "Ion" | 34
+(5 rows)
+
+SELECT * FROM cypher('order_by', $$
+ MATCH (u) WHERE EXISTS(u.name) RETURN u.name AS name, u.age AS age
ORDER BY age ASC, name DESCENDING
+$$) AS (name agtype, age agtype);
+ name | age
+---------+-----
+ "Jill" | 23
+ "Jerry" | 34
+ "Ion" | 34
+ "John" | 38
+ "Mary" | 57
+(5 rows)
+
+SELECT * FROM cypher('order_by', $$
+ MATCH (u) WHERE EXISTS(u.name) RETURN u.name AS name, u.age AS age
ORDER BY age DESC, name ASCENDING
+$$) AS (name agtype, age agtype);
+ name | age
+---------+-----
+ "Mary" | 57
+ "John" | 38
+ "Ion" | 34
+ "Jerry" | 34
+ "Jill" | 23
+(5 rows)
+
--CASE
SELECT create_graph('case_statement');
NOTICE: graph "case_statement" has been created
diff --git a/regress/sql/expr.sql b/regress/sql/expr.sql
index 466bace4..7bf1f26b 100644
--- a/regress/sql/expr.sql
+++ b/regress/sql/expr.sql
@@ -2823,6 +2823,7 @@ SELECT * FROM cypher('order_by', $$CREATE ({i: false})$$)
AS (result agtype);
SELECT * FROM cypher('order_by', $$CREATE ({i: {key: 'value'}})$$) AS (result
agtype);
SELECT * FROM cypher('order_by', $$CREATE ({i: [1]})$$) AS (result agtype);
+
SELECT * FROM cypher('order_by', $$
MATCH (u)
RETURN u.i
@@ -2835,6 +2836,35 @@ SELECT * FROM cypher('order_by', $$
ORDER BY u.i DESC
$$) AS (i agtype);
+--
+-- Test ORDER BY with AS
+--
+SELECT * FROM cypher('order_by', $$ CREATE ({name: 'John', age: 38}) $$) AS
(result agtype);
+SELECT * FROM cypher('order_by', $$ CREATE ({name: 'Jill', age: 23}) $$) AS
(result agtype);
+SELECT * FROM cypher('order_by', $$ CREATE ({name: 'Ion', age: 34}) $$) AS
(result agtype);
+SELECT * FROM cypher('order_by', $$ CREATE ({name: 'Mary', age: 57}) $$) AS
(result agtype);
+SELECT * FROM cypher('order_by', $$ CREATE ({name: 'Jerry', age: 34}) $$) AS
(result agtype);
+
+SELECT * FROM cypher('order_by', $$
+ MATCH (u) WHERE EXISTS(u.name) RETURN u.name AS name, u.age AS age
ORDER BY name
+$$) AS (name agtype, age agtype);
+
+SELECT * FROM cypher('order_by', $$
+ MATCH (u) WHERE EXISTS(u.name) RETURN u.name AS name, u.age AS age
ORDER BY name ASC
+$$) AS (name agtype, age agtype);
+
+SELECT * FROM cypher('order_by', $$
+ MATCH (u) WHERE EXISTS(u.name) RETURN u.name AS name, u.age AS age
ORDER BY name DESC
+$$) AS (name agtype, age agtype);
+
+SELECT * FROM cypher('order_by', $$
+ MATCH (u) WHERE EXISTS(u.name) RETURN u.name AS name, u.age AS age
ORDER BY age ASC, name DESCENDING
+$$) AS (name agtype, age agtype);
+
+SELECT * FROM cypher('order_by', $$
+ MATCH (u) WHERE EXISTS(u.name) RETURN u.name AS name, u.age AS age
ORDER BY age DESC, name ASCENDING
+$$) AS (name agtype, age agtype);
+
--CASE
SELECT create_graph('case_statement');
SELECT * FROM cypher('case_statement', $$CREATE ({id: 1, i: 1, j:
null})-[:connected_to {id: 1, k:0}]->({id: 2, i: 'a', j: 'b'})$$) AS (result
agtype);
diff --git a/src/backend/parser/cypher_clause.c
b/src/backend/parser/cypher_clause.c
index 8383b340..a5413bda 100644
--- a/src/backend/parser/cypher_clause.c
+++ b/src/backend/parser/cypher_clause.c
@@ -2296,6 +2296,32 @@ static TargetEntry
*find_target_list_entry(cypher_parsestate *cpstate,
ListCell *lt;
TargetEntry *te;
+ /*
+ * If the ORDER BY item is a simple identifier, check if it matches
+ * an alias in the target list. This implements SQL99-compliant
+ * alias matching for ORDER BY clauses.
+ */
+ if (IsA(node, ColumnRef))
+ {
+ ColumnRef *cref = (ColumnRef *)node;
+
+ if (list_length(cref->fields) == 1)
+ {
+ char *name = strVal(linitial(cref->fields));
+
+ /* Try to match an alias in the target list */
+ foreach (lt, *target_list)
+ {
+ te = lfirst(lt);
+
+ if (te->resname != NULL && strcmp(te->resname, name) == 0)
+ {
+ return te;
+ }
+ }
+ }
+ }
+
expr = transform_cypher_expr(cpstate, node, expr_kind);
foreach (lt, *target_list)
