Repository navigation
Experimental Cypher: ORDER BY after aggregation or DISTINCT accepts a dropped binding and sorts by an arbitrary row #313
Description
What happened
With the experimental engine, a RETURN that aggregates or uses DISTINCT accepts ORDER BY on a variable the projection dropped, and sorts by whatever row the group happened to keep. ASC and DESC can come back in the same order.
Graph:
CREATE (a:Person {id: 1, name: 'Ann'})-[:KNOWS {since: 2020}]->(b:Person {id: 2, name: 'Bob', age: 25})
CREATE (a:Person {id: 1})-[:KNOWS {since: 2021}]->(c:Person {id: 3, name: 'Cid', age: 35})
CREATE (d:Person {id: 4, name: 'Dee'})-[:KNOWS {since: 2019}]->(b:Person {id: 2})
CREATE (d:Person {id: 4})-[:KNOWS {since: 2023}]->(e:Person {id: 5, name: 'Eve', age: 20})| Query | Experimental | Legacy |
|---|---|---|
MATCH (a:Person)-[:KNOWS]->(b) RETURN a.name AS name, count(b) AS c ORDER BY b.age |
Ann, Dee | error: aggregate ORDER BY cannot reference row properties |
same, ORDER BY b.age DESC |
Ann, Dee | error |
MATCH (a:Person)-[r:KNOWS]->(b) RETURN a.name AS name, count(b) AS c ORDER BY r.since |
Dee, Ann | error |
same, ORDER BY r.since DESC |
Ann, Dee | error |
MATCH (a:Person)-[:KNOWS]->(b) RETURN DISTINCT a.name AS name ORDER BY b.age |
Dee, Ann | error: ORDER BY expressions with RETURN DISTINCT must be projected by the RETURN clause |
After the aggregation there is no b or r in the result, so the sort has no row to read; it is evaluated against one of the binding rows of each group.
The WITH form is already refused: WITH a.name AS name, count(b) AS c ORDER BY b.age RETURN name, c fails with ORDER BY after WITH references out-of-scope binding b.
What you expected
The same refusal as WITH and as the legacy engine. The openCypher TCK asks for a compile-time UndefinedVariable in both forms: ReturnOrderBy2 scenario 13 (RETURN DISTINCT a.name ORDER BY a.age) and ReturnOrderBy6 scenario 4 (RETURN count(you.age) AS agg ORDER BY me.age + count(you.age)).
Sorting by a returned alias, by a returned expression written out again (RETURN me.age AS age, count(you.age) AS cnt ORDER BY me.age + count(you.age), ReturnOrderBy6 scenario 3), and by an aggregate should keep working. A RETURN without aggregation or DISTINCT should keep sorting by bindings it does not return.
Steps to reproduce
The query, if any:
MATCH (a:Person)-[:KNOWS]->(b) RETURN a.name AS name, count(b) AS c ORDER BY b.age DESC- Create the graph above.
- Run the queries in the table with
CypherEngineMode::Experimental. - Run the same queries with the legacy engine, or with
WITHin place ofRETURN, and compare.
Environment
- HydraDB version (image tag or commit): 07457a8 (main)
- Deployment: other, local Rust build on macOS, direct
GraphShardAPI against a local object store CLOUD_PROVIDER: local- Client: other,
GraphShard::execute_cypher_rowswithQueryContext::with_cypher_engine(CypherEngineMode::Experimental)
Logs
Nothing is logged; the queries succeed with the rows above.
The cause is in lower_query (crates/cypher-engine/src/plan/lower.rs): the RETURN sort is checked against every visible binding plus the returned aliases, for every RETURN, while lower_with checks a sort against the projected names only. I have a fix that applies the WITH rule to an aggregating or DISTINCT RETURN, with returned expressions and aggregates still allowed as in the TCK, and the four cases above as tests. I would like to open the PR for this issue.
Activity