Query variable binding and the position of SKIP

(Aanastasiou) #1

Hello everyone

I have spotted the following behaviour and I thought I would confirm if it is intended:

  1. SKIP is ignored if it follows LIMIT but the query passes validation (?)
  2. Ranges are valid for lists but not for strings (?).
  3. If you "use" a variable in a RETURN clause you cannot re-use it in the ORDER BY clause?

For example for 3, you can MATCH (a:something)-[r:something_else]-(b:whatever) return r.a_field order by r.a_field but you cannot MATCH (a:something)-[r:something_else]-(b:whatever) return split(r.a_field,"|") order by size(r.a_field). For the latter to work, you have to MATCH (a:something)-[r:something_else]-(b:whatever) return r.a_field, split(r.a_field,"|") order by size(r.a_field). Is this intentional?

Server 3.4.5, Browser 3.2.5, APOC

All the best

(Andrew Bowman) #2

Thanks for reporting, let's take a look. I'm using Neo4j 3.4.5 and browser 3.2.5 to verify (APOC isn't in use in any of the queries here so disregarding it)

  1. I can't replicate this. Can you provide a sample dataset and query where this happens?

  2. You are correct, but it would be really neat to support index ranges for substring operations. We'll add it as a suggestion.

  3. All of these queries are working for me. Could you try replicating on the Movies graph, and if you can replicate the issue can you provide the query?

(Aanastasiou) #3

Hello Andrew

Thanks for letting me know.

  1. With the movie database:

This MATCH (a:Person)-[:ACTED_IN]-(b:Movie) RETURN DISTINCT a.name LIMIT 10 returns:

Emil Eifrem
Hugo Weaving
Laurence Fishburne
Carrie-Anne Moss
Keanu Reeves
Al Pacino
Charlize Theron
Aaron Sorkin
Christopher Guest
Kevin Pollak

Now this MATCH (a:Person)-[:ACTED_IN]-(b:Movie) RETURN DISTINCT a.name SKIP 2 LIMIT 5, starts on Laurence Fishburne and stops at Charlize Theron. But this MATCH (a:Person)-[:ACTED_IN]-(b:Movie) RETURN DISTINCT a.name LIMIT 5 SKIP 2 starts at Emil Eifrem and stops at Keanu Reeves.

And I just noticed that the parser has basically cut the "SKIP 2" part out completely because in the result set window (in the browser), the query that is depicted on the top does not include the "SKIP" clause.

From a parsing point of view, I understand why the clause is ignored but I feel that it should complain because otherwise you still get back results but they are not as expected.

  1. Fantastic

  2. This may have been the byproduct of another error that was not reported as such. The original query that was giving me this error was using apoc.text.join in conjuction with split. This was a quick test to "group by" a specific subset of terms contained in a string. In reality, these terms have already been expanded to the graph they represent and the query will be adapted accordingly. The bottom line is that I came across this error trying to split a string and then re-compose a subset of it.

Here is what I am faced with at the moment trying to replicate the error on the Movie db, which is a bit worrying:

CALL apoc.text.phonetic("Alpha") returns A410.

MATCH (a:Person)-[:ACTED_IN]-(b:Movie) RETURN apoc.text.phonetic(a.name) fails with "Unknown function apoc.text.phonetic...."

CALL dbms.procedures() clearly lists apoc.text.phonetic with two prototypes, one with value::ANY and one with value:STRING and dbms.security.procedures.unrestricted=apoc.*.

What I suspect might have happened originally is the apoc function failing silently and reporting a different error. I will try the query on my data later on again and provide an update.

All the best

(Andrew Bowman) #4

Quick thing to try for 1, please make sure in the Browser Settings (gear in the lower-left) that the "Enable multi statement query editor" is unchecked, as that feature still has some lingering bugs related to query parsing, that could be the cause of what's going on.

(Andrew Bowman) #5

Concerning 3, it's important to know the difference of usage between a procedure and a function.

apoc.text.phonetic() is a procedure, not a function, so you can't use it as an expression as in your query. You must use CALL and YIELD to invoke it and get results (when the query consists of ONLY a procedure call, you can omit the YIELD and it will behave as if all possible yielded values were yielded and returned, but when it's not the only clause in the query you have to use both CALL and YIELD).

(Aanastasiou) #6

Indeed, thank you very much.

The API parser complains alright anyway but the browser is where I try certain scripts.

(Aanastasiou) #7

I appreciate the difference between a procedure and a function.

The trouble here is that I did not note the error down. The error was being raised as I was trying to recreate a string by a subset of its parts. This has been properly resolved to a graph structure now and the query has changed along too. It was persistent enough to note it down but I cannot re-create it at the moment (which is frustrating in itself) so if I come across it again, I will re-post it as a continuation of this thread.

All the best