CALL (country, states){
MATCH (states)-[:has]->(district:District)
WHERE NOT district.id IN $distId
RETURN district AS result
UNION
OPTIONAL MATCH (country)-[:has*1..2]-(district:District)
WHERE NOT district.id IN $distId
RETURN district AS result
}
RETURN result
I'm querying with the given data model using the latest Cypher subquery syntax, but I'm encountering an error with empty.tail . It works fine with the old syntax. As call {WITH } syntax
is
CALL (country, states){
MATCH (states)-[:has]->(district:District)
WHERE NOT district.id IN $distId
RETURN district AS result
UNION
OPTIONAL MATCH (country)-[:has*1..2]-(district:District)
WHERE NOT district.id IN $distId
RETURN district AS result
}
RETURN result
the entire cypher/query? i tried on 5.24.2 and get a different error
/single/instance1/neo4j-enterprise-5.24.2/bin$ ./cypher-shell
Connected to Neo4j using Bolt protocol version 5.6 at neo4j://localhost:7687.
Type :help for a list of available commands or :exit to exit the shell.
Note that Cypher queries must end with a semicolon.
@neo4j> CALL (country, states){
MATCH (states)-[:has]->(district:District)
WHERE NOT district.id IN $distId
RETURN district AS result
UNION
OPTIONAL MATCH (country)-[:has*1..2]-(district:District)
WHERE NOT district.id IN $distId
RETURN district AS result
}
RETURN result;
Variable `country` not defined (line 1, column 7 (offset: 6))
"CALL (country, states){"
This is a partial query with an example context:
There is a one-to-many relationship between Country and State
MATCH (country:Country)-[:has]->(states:State)
WHERE country.name STARTS WITH 'In'
CALL (country, states) {
MATCH (states)-[:has]->(district:District)
WHERE NOT district.id IN $distId
RETURN district AS result
UNION
OPTIONAL MATCH (country)-[:has*1..2]-(district:District)
WHERE NOT district.id IN $distId
RETURN district AS result
}
RETURN result
Assume there are two countries with names starting with “In”:
India with 28 states
Indonesia with 38 states
In this setup, you can expect a total of 66 input rows passed to the CALL (country, states) subquery, corresponding to the combined states from each country.
@gopiselvam96 Thanks for reporting this issue. I've been trying to reproduce the error on a local 5.24.2 instance but without success.
Would it be possible for you to attach the query you were using with the old syntax, as well as the plans for both the old and new queries? (this can be done through using the EXPLAIN keyword in the beginning of the query)
Any further information you would be able to provide concerning the dataset and parameters used would also be greatly appreciated and could help us reproduce and fix the issue.
In case you can't get your syntax working, maybe the this refactored version will work for you:
MATCH (country:Country)
WHERE country.name STARTS WITH 'In'
UNWIND
[(country)-[:has]->(:State)-[:has]->(district:District) WHERE NOT district.id IN $distId | district] +
[(country)-[:has*1..2]-(district:District) WHERE NOT district.id IN $distId | district] as result
return result
Out if curiosity, why doesn’t the single pattern work for both patterns:
(country)-[:has*1..2]-(district:District) WHERE NOT district.id IN $distId
We recently migrated to Neo4j version 5.24.1. As part of this migration, we’re removing deprecated functions, including updating the CALL {WITH var} syntax to the new CALL (var) {} syntax as recommended in the documentation (Deprecations, additions, and compatibility - Cypher Manual).
During this process, we encountered issues with some of our queries with error empty.tail.
I attempted to replicate the exact issue we’re facing by using mock data.
Steps to reproduce:
Please run the Cypher queries in the database to populate this sample data.
CREATE (n8:Genre {name: "Comedy"})<-[:TYPE_OF]-(potc:Movie {name: "Pirates of the Caribbean", id: 10001})-[:TYPE_OF]->(n7:Genre {name: "Adventure"})<-[:TYPE_OF]-(n3:Movie {name: "Alice in Wonderland", id: 10002})<-[:ACTED_IN]-(johny:Person {name: "Johnny Depp"})-[:ACTED_IN]->(potc)<-[:ACTED_IN]-(:Person {name: "Keira Knightley"})-[:STAYS_IN]->(usa:Country {name: "America"}),
(usa)<-[:STAYS_IN]-(johny)-[:ACTED_IN]->(n4:Movie {name: " Secret Window", id: 10003})-[:TYPE_OF]->(n7),
(:Description {name: "Pirate movie"})<-[:DESCRIBE]-(potc)-[:PART_OF]->(:Movie {name: "The Curse of the Black Pearl", id: 10011}),
(:Description {name: "Fantasy Movie"})<-[:DESCRIBE]-(n3)-[:TYPE_OF]->(n8),
(n4)-[:DESCRIBE]->(:Description {name: "I didnt watch yet"})
The query where we’re encountering the issue is as follows:
=================query 1 starts =======================
MATCH (movie:Movie) where movie.name = 'Pirates of the Caribbean'
MATCH (movie)-[:(ACTED_IN|PART_OF)*1..2]-(actor:Person)-[:ACTED_IN]->(otherMovies:Movie)-[:TYPE_OF]->(:Genre{name:'Adventure'}) WHERE exists((actor)-[:STAYS_IN]->(:Country{name:'America'}))
CALL (movie, otherMovies){
MATCH (otherMovies)-[:DESCRIBE]->(desc:Description)
WHERE NOT desc.name IN ['I didnt watch yet']
RETURN desc AS result
UNION
MATCH (movie)-[:DESCRIBE]->(desc:Description)
WHERE NOT desc.name IN ['I didnt watch yet']
RETURN desc AS result
}
RETURN result
================= query 1 ends ============================
If we replace the new syntax with the deprecated CALL {WITH var}, the query is working fine
================== query2 starts ======================
MATCH (movie:Movie) where movie.name = 'Pirates of the Caribbean'
MATCH (movie)-[:(ACTED_IN|PART_OF)*1..2]-(actor:Person)-[:ACTED_IN]->(otherMovies:Movie)-[:TYPE_OF]->(:Genre{name:'Adventure'}) WHERE exists((actor)-[:STAYS_IN]->(:Country{name:'America'}))
CALL {
WITH movie, otherMovies
MATCH (otherMovies)-[:DESCRIBE]->(desc:Description)
WHERE NOT desc.name IN ['I didnt watch yet']
RETURN desc AS result
UNION
MATCH (movie)-[:DESCRIBE]->(desc:Description)
WHERE NOT desc.name IN ['I didnt watch yet']
RETURN desc AS result
}
RETURN result
================= query2 ends ===================================
I also noticed that if I run with nested CALL (){CALL (){}} syntax the query is working fine
================ query 3 starts ===============================
MATCH (movie:Movie) where movie.name = 'Pirates of the Caribbean'
MATCH (movie)-[:(ACTED_IN|PART_OF)*1..2]-(actor:Person)-[:ACTED_IN]->(otherMovies:Movie)-[:TYPE_OF]->(:Genre{name:'Adventure'}) WHERE exists((actor)-[:STAYS_IN]->(:Country{name:'America'}))
CALL (movie, otherMovies){
CALL (movie, otherMovies){
MATCH (otherMovies)-[:DESCRIBE]->(desc:Description)
WHERE NOT desc.name IN ['I didnt watch yet']
RETURN desc AS result
UNION
MATCH (movie)-[:DESCRIBE]->(desc:Description)
WHERE NOT desc.name IN ['I didnt watch yet']
RETURN desc AS result
}
RETURN result
}
RETURN result
===========query 3 ends ==============================
================== debug.log start ==========================
2024-11-02 08:48:56.247+0000 ERROR [o.n.b.f.StateMachineImpl] Client triggered an unexpected error [Neo.DatabaseError.General.UnknownError]: empty.tail, reference 29f2498e-5630-4364-8bfd-95959e80a080.
2024-11-02 08:48:56.247+0000 ERROR [o.n.b.f.StateMachineImpl] Client triggered an unexpected error [Neo.DatabaseError.General.UnknownError]: empty.tail, reference 29f2498e-5630-4364-8bfd-95959e80a080.
org.neo4j.bolt.protocol.common.fsm.error.TransactionStateTransitionException: empty.tail
at org.neo4j.bolt.protocol.common.fsm.transition.transaction.CreateStatementStateTransition.process(CreateStatementStateTransition.java:56) ~[neo4j-bolt-5.24.1.jar:5.24.1]
at org.neo4j.bolt.protocol.common.fsm.transition.transaction.CreateStatementStateTransition.process(CreateStatementStateTransition.java:32) ~[neo4j-bolt-5.24.1.jar:5.24.1]
at org.neo4j.bolt.protocol.common.fsm.transition.transaction.TransactionalStateTransition.process(TransactionalStateTransition.java:42) ~[neo4j-bolt-5.24.1.jar:5.24.1]
at org.neo4j.bolt.fsm.state.MultiTransitionState.process(MultiTransitionState.java:53) ~[neo4j-bolt-5.24.1.jar:5.24.1]
at org.neo4j.bolt.fsm.StateMachineImpl.process(StateMachineImpl.java:159) ~[neo4j-bolt-5.24.1.jar:5.24.1]
at org.neo4j.bolt.protocol.common.connector.connection.AtomicSchedulingConnection$ProcessJob.perform(AtomicSchedulingConnection.java:634) ~[neo4j-bolt-5.24.1.jar:5.24.1]
at org.neo4j.bolt.protocol.common.connector.connection.AtomicSchedulingConnection.executeJob(AtomicSchedulingConnection.java:338) ~[neo4j-bolt-5.24.1.jar:5.24.1]
at org.neo4j.bolt.protocol.common.connector.connection.AtomicSchedulingConnection.doExecuteJobs(AtomicSchedulingConnection.java:317) ~[neo4j-bolt-5.24.1.jar:5.24.1]
at org.neo4j.bolt.protocol.common.connector.connection.AtomicSchedulingConnection.executeJobs(AtomicSchedulingConnection.java:212) ~[neo4j-bolt-5.24.1.jar:5.24.1]
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539) [?:?]
at java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) [?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) [?:?]
at java.lang.Thread.run(Thread.java:833) [?:?]
Caused by: org.neo4j.bolt.tx.error.statement.StatementExecutionException: empty.tail
at org.neo4j.bolt.tx.TransactionImpl.run(TransactionImpl.java:143) ~[neo4j-bolt-5.24.1.jar:5.24.1]
at org.neo4j.bolt.protocol.common.fsm.transition.transaction.CreateStatementStateTransition.process(CreateStatementStateTransition.java:48) ~[neo4j-bolt-5.24.1.jar:5.24.1]
... 13 more
Caused by: java.util.NoSuchElementException: empty.tail
at scala.collection.immutable.Vector.last(Vector.scala:292) ~[scala-library-2.13.11.jar:?]
at org.neo4j.cypher.internal.ast.SingleQuery.finalScope(Query.scala:587) ~[neo4j-ast-5.24.1.jar:5.24.1]
at org.neo4j.cypher.internal.ast.UnmappedUnion.$anonfun$checkColumnNamesAgree$1(Query.scala:886) ~[neo4j-ast-5.24.1.jar:5.24.1]
at org.neo4j.cypher.internal.ast.semantics.SemanticCheckInterpreter$.run(SemanticCheckInterpreter.scala:44) ~[neo4j-ast-5.24.1.jar:5.24.1]
at org.neo4j.cypher.internal.ast.semantics.SemanticCheckInterpreter$.runCheck(SemanticCheckInterpreter.scala:28) ~[neo4j-ast-5.24.1.jar:5.24.1]
at org.neo4j.cypher.internal.ast.semantics.SemanticCheck.run(SemanticCheck.scala:39) ~[neo4j-ast-5.24.1.jar:5.24.1]
at org.neo4j.cypher.internal.ast.semantics.SemanticCheck.run$(SemanticCheck.scala:38) ~[neo4j-ast-5.24.1.jar:5.24.1]
at org.neo4j.cypher.internal.ast.semantics.SemanticCheck$FlatMap.run(SemanticCheck.scala:155) ~[neo4j-ast-5.24.1.jar:5.24.1]
at org.neo4j.cypher.internal.ast.semantics.SemanticChecker$.check(SemanticChecker.scala:29) ~[neo4j-ast-5.24.1.jar:5.24.1]
at org.neo4j.cypher.internal.frontend.phases.SemanticAnalysis.process(SemanticAnalysis.scala:59) ~[neo4j-front-end-5.24.1.jar:5.24.1]
at org.neo4j.cypher.internal.frontend.phases.SemanticAnalysis.process(SemanticAnalysis.scala:44) ~[neo4j-front-end-5.24.1.jar:5.24.1]
at org.neo4j.cypher.internal.frontend.phases.Phase.$anonfun$transform$1(Phase.scala:36) ~[neo4j-front-end-5.24.1.jar:5.24.1]
at org.neo4j.cypher.internal.frontend.helpers.package$.$anonfun$closing$1(package.scala:25) ~[neo4j-front-end-5.24.1.jar:5.24.1]
at org.neo4j.cypher.internal.frontend.helpers.package$.using(package.scala:34) ~[neo4j-front-end-5.24.1.jar:5.24.1]
at org.neo4j.cypher.internal.frontend.helpers.package$.closing(package.scala:25) ~[neo4j-front-end-5.24.1.jar:5.24.1]
at org.neo4j.cypher.internal.frontend.phases.Phase.transform(Phase.scala:35) ~[neo4j-front-end-5.24.1.jar:5.24.1]
at org.neo4j.cypher.internal.frontend.phases.Phase.transform$(Phase.scala:33) ~[neo4j-front-end-5.24.1.jar:5.24.1]
at org.neo4j.cypher.internal.frontend.phases.SemanticAnalysis.transform(SemanticAnalysis.scala:44) ~[neo4j-front-end-5.24.1.jar:5.24.1]
at org.neo4j.cypher.internal.frontend.phases.PipeLine.transform(Transformer.scala:103) ~[neo4j-front-end-5.24.1.jar:5.24.1]
at org.neo4j.cypher.internal.frontend.phases.PipeLine.transform(Transformer.scala:102) ~[neo4j-front-end-5.24.1.jar:5.24.1]
at org.neo4j.cypher.internal.frontend.phases.PipeLine.transform(Transformer.scala:102) ~[neo4j-front-end-5.24.1.jar:5.24.1]
at org.neo4j.cypher.internal.frontend.phases.PipeLine.transform(Transformer.scala:102) ~[neo4j-front-end-5.24.1.jar:5.24.1]
at org.neo4j.cypher.internal.frontend.phases.PipeLine.transform(Transformer.scala:102) ~[neo4j-front-end-5.24.1.jar:5.24.1]
at org.neo4j.cypher.internal.frontend.phases.PipeLine.transform(Transformer.scala:102) ~[neo4j-front-end-5.24.1.jar:5.24.1]
at org.neo4j.cypher.internal.frontend.phases.PipeLine.transform(Transformer.scala:102) ~[neo4j-front-end-5.24.1.jar:5.24.1]
at org.neo4j.cypher.internal.frontend.phases.PipeLine.transform(Transformer.scala:102) ~[neo4j-front-end-5.24.1.jar:5.24.1]
at org.neo4j.cypher.internal.frontend.phases.PipeLine.transform(Transformer.scala:102) ~[neo4j-front-end-5.24.1.jar:5.24.1]
at org.neo4j.cypher.internal.frontend.phases.PipeLine.transform(Transformer.scala:102) ~[neo4j-front-end-5.24.1.jar:5.24.1]
at org.neo4j.cypher.internal.compiler.CypherParsing.parseQuery(CypherParsing.scala:98) ~[neo4j-cypher-planner-5.24.1.jar:5.24.1]
at org.neo4j.router.impl.query.QueryProcessorImpl.parse(QueryProcessorImpl.java:273) ~[neo4j-query-router-5.24.1.jar:5.24.1]
at org.neo4j.router.impl.query.QueryProcessorImpl.prepareQueryForCache(QueryProcessorImpl.java:168) ~[neo4j-query-router-5.24.1.jar:5.24.1]
at org.neo4j.router.impl.query.QueryProcessorImpl.getFromCache(QueryProcessorImpl.java:150) ~[neo4j-query-router-5.24.1.jar:5.24.1]
at org.neo4j.router.impl.query.QueryProcessorImpl.processQuery(QueryProcessorImpl.java:104) ~[neo4j-query-router-5.24.1.jar:5.24.1]
at org.neo4j.router.impl.QueryRouterImpl.executeQuery(QueryRouterImpl.java:229) ~[neo4j-query-router-5.24.1.jar:5.24.1]
at org.neo4j.router.impl.bolt.QueryRouterBoltSpi$Transaction.executeQuery(QueryRouterBoltSpi.java:161) ~[neo4j-query-router-5.24.1.jar:5.24.1]
at org.neo4j.bolt.tx.TransactionImpl.run(TransactionImpl.java:136) ~[neo4j-bolt-5.24.1.jar:5.24.1]
at org.neo4j.bolt.protocol.common.fsm.transition.transaction.CreateStatementStateTransition.process(CreateStatementStateTransition.java:48) ~[neo4j-bolt-5.24.1.jar:5.24.1]
... 13 more
================== debug.log end ==========================
Please let us know why the new CALL (var) { ... UNION ... } syntax is resulting in an empty.tail exception.
Could you advise if there is an issue with the way we’re writing these Cypher queries? Alternatively, if this is a Neo4j-related issue, please keep us updated
The new syntax works if you insert a "WITH" before. Not sure why. This doesn't seem to be expected behavior.
MATCH (movie:Movie) where movie.name = 'Pirates of the Caribbean'
MATCH (movie)-[:(ACTED_IN|PART_OF)*1..2]-(actor:Person)-[:ACTED_IN]->(otherMovies:Movie)-[:TYPE_OF]->(:Genre{name:'Adventure'}) WHERE exists((actor)-[:STAYS_IN]->(:Country{name:'America'}))
WITH movie, otherMovies
CALL (movie, otherMovies){
MATCH (otherMovies)-[:DESCRIBE]->(desc:Description)
WHERE NOT desc.name IN ['I didnt watch yet']
RETURN desc AS result
UNION
MATCH (movie)-[:DESCRIBE]->(desc:Description)
WHERE NOT desc.name IN ['I didnt watch yet']
RETURN desc AS result
}
RETURN result
Actually, the results are not the same:
Ok, they are the same when the import in the "with" is added by both branches of the "UNION" query.
Thank you @gopiselvam96 for reporting and the steps to reproduce, it was very helpful! We have now been able to confirm the issue and confirmed that this bug is fixed. The fix is included in the latest release 5.25.1 which is currently available.
Let us know if you have any further issues or unexpected behaviors.
