Possible Cypher Bug using Limit duplicates line

[Reiner (Clooney24)]

Running on 4.4.2 enterprise version, I'm facing a very strange problem. I'm sorry that I can't expose the cypher or sample data here. Before digging deeper, maybe someone can just tell if this can be properly explained or if it looks like bug not only to me.

We are running a quite complex cypher with almost 80 lines but at the end, all is reduced to just 2 properties and the correct result of 3 rows.

But adding the clause "LIMIT 15" duplicates one of those rows and doesn't respect the ordering:

Using LIMIT 15 at the end after the return clause still result in incorrect 4 rows:

Surprisingly, using LIMIT with a value of 10 instead of 15 will bring the correct 3 rows:

And also a LIMIT to 50 will result in 3 rows. The error occurs only with values between 11 and 45:

Even using DISTINCT will still duplicate this row even with the same values.

Can someone explain how this can happen?

[Reiner (Clooney24)]

Just recognized that the order is wrong in all snippets.

here the result code formatted.

[
  {
    "keys": [
      "title",
      "lastrequest"
    ],
    "length": 2,
    "_fields": [
      "(Junior) Relationship Manager / Sales Manager (m/w/d)",
      {
        "low": -2145425746,
        "high": 395
      }
    ],
    "_fieldLookup": {
      "title": 0,
      "lastrequest": 1
    }
  },
  {
    "keys": [
      "title",
      "lastrequest"
    ],
    "length": 2,
    "_fields": [
      "Marketing Assistenz (m/w/d)",
      {
        "low": 955165344,
        "high": 377
      }
    ],
    "_fieldLookup": {
      "title": 0,
      "lastrequest": 1
    }
  },
  {
    "keys": [
      "title",
      "lastrequest"
    ],
    "length": 2,
    "_fields": [
      "Marketing/Sales Assistent (m/w/d)",
      {
        "low": 547609158,
        "high": 376
      }
    ],
    "_fieldLookup": {
      "title": 0,
      "lastrequest": 1
    }
  },
  {
    "keys": [
      "title",
      "lastrequest"
    ],
    "length": 2,
    "_fields": [
      "(Junior) Relationship Manager / Sales Manager (m/w/d)",
      {
        "low": -2145425746,
        "high": 395
      }
    ],
    "_fieldLookup": {
      "title": 0,
      "lastrequest": 1
    }
  }
]

[Reiner (Clooney24)]

After deeper investigation the cause are these red marked parts of a pattern comprehension somewhere in the middle of our cypher. When I remove these parts, the result shows the expected 3 rows and come in correct order, when I let them in, I get 4 rows with 1 duplicate and wrong order. ... but why?

It also works fine, when I cut out another part

[Reiner (Clooney24)]

And another strange effect, when I aggregate lastrequest like

max(lastrequest) as lastrequest in the last with-clause, I get 3 rows with correct order again....

so both identical lastrequest values where not properly aggregated and not properly ordered... but ONLY when adding a LIMIT 15 at the end? I really start loosing confidence ..

[arne.fischereit]

Hi Reiner,

This looks indeed odd. It is, however, hard to assess without knowing the query plan. So, if you could provide that for the problematic cases, we could investigate this further.

As you seem to be using Neo4j Enterprise, it might make things easier if you (in confidence) contact Neo4j Support to disclose more information. Is that an option?

[Reiner (Clooney24)]

Hi Arne, plans attached. I could find a line that causes the effect. In lines 33 and 35 and use pattern comprehensions to calculate a message count and unread message count. Line 35 is the problem. When I use size() or tail() or just the output of the pattern comprehension, the order is wrong because those values in lastrequest seems not be that what is shown. That's why order is wrong and they don't get grouped later (i cutted out that later grouping part to see clearer results of that part). The attached plan.png is for that first result.

But when I use head() in line 35 everything is fine (plan_head.png is for that result)

Also when I completely skip that pattern comprehension and just fill 0 into that varialbe, it works fine

This is plan.png for the first result shown above:

This is plan_head.png for the second result shown above:

My workaround for now is to transform lastRequest to toString(lastRequest) to force values that look identical to be truly identical - then grouping and ordering works correctly in the end.

If you like, we can do a teams sessions.

Best, Reiner

[arne.fischereit]

Hi Reiner,

I cannot see anything suspicious in the plan. I guess it would be best if you could open up a support case to have this looked at.

Regards,

Arne