order by report column not found while it does exists

[nebula-bots]

Please check the FAQ documentation before raising an issue

Describe the bug (required)

Look at the query in Nebula below, Nebula report column a0 not found, while it does exists:

(root@nebula) [gdlancer]> MATCH (v)-->(t) WHERE id(v) in [1, 2, 3, 4, 5, 6, 7] WITH v, t, count(t) as a0 WHERE a0 > 1 return id(v) AS r0, id(t) AS r1 ORDER BY a0
[ERROR (-1009)]: SemanticError: Column `a0' not found

Thu, 24 Nov 2022 12:00:26 CST

(root@nebula) [gdlancer]>

In contrast, Neo4j return expected result:

$ MATCH (v)-->(t) WHERE v.id in [1, 2, 3, 4, 5, 6, 7] WITH v, t, count(t) as a0 WHERE a0 > 1 return v.id AS r0, t.id AS r1 ORDER BY a0

╒════╤════╕
│"r0"│"r1"│
╞════╪════╡
│3   │6   │
├────┼────┤
│5   │24  │
├────┼────┤
│6   │23  │
├────┼────┤
│4   │13  │
├────┼────┤
│4   │20  │
├────┼────┤
│2   │2   │
└────┴────┘

Your Environments (required)

• OS: uname -a
• Compiler: g++ --version or clang++ --version
• CPU: lscpu
• Commit id (e.g. a3ffc7d8) be2c294

How To Reproduce(required)

Steps to reproduce the behavior:

1. Step 1
2. Step 2
3. Step 3

Expected behavior

Additional context

[xtcyclist]

MATCH (v)-->(t) WHERE id(v) in [1, 2, 3, 4, 5, 6, 7] WITH v, t, count(t) as a0 WHERE a0 > 1 return id(v) AS r0, id(t) AS r1 ORDER BY a0

This query first finds all (v)-->(t) patterns, in which distinct edges duplicate their coresponding matches. For example, ("X")-[e:like]-("Y") and ("X")-[e:teammate]-("Y") will introduce two ("X")-->("Y").

The with clause with a count(t) as a0 then aggregates them. ("X")-->("Y") will have an a0 column, the content of which is 2, which satisfies a0 > 1.

The return clause does the projection. And, this projection does not alter the cadinality of the data set before it, since it is only deriving the id() of each v and t.

Thus, according to the definition of ORDER BY in cypher, all variables in the with clause before the projection should be available to the ORDER BY.

Reference: Cypher Query Language Reference, Version 9

In terms of scope of variables, ORDER BY follows special rules, depending on if the projecting RETURN or WITH clause is either aggregating or DISTINCT. If it is an aggregating or DISTINCT projection, only the variables available in the projection are available. If the projection does not alter the output cardinality (which aggregation and DISTINCT do), variables available from before the projecting clause are also available. When the projection clause shadows already existing variables, only the new variables are available.
Lastly, it is not allowed to use aggregating expressions in the ORDER BY sub-clause if they are not also listed in the projecting clause. This last rule is to make sure that ORDER BY does not change the results, only the order of them.

[xtcyclist]

  if (rctx->order != nullptr) {
    auto orderPlan = std::make_unique<OrderByClausePlanner>()->transform(rctx->order.get());
    NG_RETURN_IF_ERROR(orderPlan);
    auto plan = std::move(orderPlan).value();
    subPlan = SegmentsConnector::addInput(plan, subPlan, true);
  }

Status ReturnClausePlanner::buildReturn() connects the output of projection to the order-by, without considering the possibility that the input of the projection could be the input of the order-by as well.

[Aman123lug]

@xtcyclist can i contribute in this repo

[xtcyclist]

@xtcyclist can i contribute in this repo

You're welcomed to start from good first issues, if you'd like to contribute.

[xtcyclist]

We have decided not to address this compatibility (with openCypher) issue. Order-by won't support sorting by columns not listed in the return clause. If a query wants to sort its results, it has to do so on a column listed in the return clause. BTW, there is no correctness issues on order-by.