Attributes in ORDER BY

[szarnyasg]

In ORDER BY clauses, we can use attributes created in the WITH/RETURN clause and also the ones that will be removed by the projection:

WITH 1 AS a, 2 AS b
RETURN a+3 AS aa
ORDER BY aa, b

Note that this is not true if there are aggregates:

WITH 1 AS a, 2 AS b
RETURN count(a) AS aa
ORDER BY aa, b

Variable `b` not defined (line 3, column 14 (offset: 55))
"ORDER BY aa, b"
              ^

Anyways, even this simple case does not work now:

WITH 1 AS x, 2 AS y
RETURN x
ORDER BY y
LIMIT 1

The reason for this is that the query plan is incorrect, as it compiles this to a sequence of a projection, sort and top operations - and the projection removes attributes that should be used for sorting.

'Production
+- 'Top 1
   +- 'Sort [expr(2, y, (y#0)) ASC NULLS FIRST]
      +- !Projection [ret(expr(1, x, (x#0)), None, (x#0))]
         +- !Projection [ret(1, (x), (x#0)), ret(2, (y), (y#0))]
            +- Dual

[szarnyasg]

cc @jmarton

[jmarton]

Let's take a look at the following query in TckEngineTest/A simple pattern with one bound endpoint - edited

MATCH (a:A)-[r:REL]->(b:B)
WITH a AS b, b AS tmp, r AS r
WITH b AS a, r
ORDER BY a.x, b.x
LIMIT 1
MATCH (a)-[r:REL]->(b)
RETURN a, r, b