Neo4j Cypher query: "Unknown identifier" when RETURN DISTINCT

Question

Why does this query work (albeit with duplicate rows):

START user=node(1197)
MATCH (user)-[:WROTE_REVIEW]->()-[:EVALUATES]->(post)
RETURN post.Id, post.Image, post.Description
ORDER BY post.CreationTime DESC;

and this one doesn't (it returns the error Unknown identifier 'post'):

START user=node(1197)
MATCH (user)-[:WROTE_REVIEW]->()-[:EVALUATES]->(post)
RETURN DISTINCT post.Id, post.Image, post.Description
ORDER BY post.CreationTime DESC;

The only difference is the DISTINCT keyword.

I found a workaround by using a WITH but it seems to me that I shouldn't have to do this.

I'm using Neo4j 2.0.0-M05. Any ideas?

Thanks!

Answer 1

This is legit.

DISTINCT removes the possibility to order by properties that are not mentioned in the DISTINCT clause, sine they might be collapsed (e.g. if there is 3 records with post.CreationTime lumped into one post.Id bucket with DISTINCT, which one should be ordered by?). To get a working query, include post.CreationTime in the DISTINCT like:

START user=node(1197)
MATCH (user)-[:WROTE_REVIEW]->()-[:EVALUATES]->(post)
RETURN DISTINCT post.Id, post.Image, post.Description, post.CreationTime
ORDER BY post.CreationTime DESC;

This is not obvious though, so I raised https://github.com/neo4j/neo4j/issues/1322 to make it into the docs - thanks for pointing it out!