Neo4j - nested FOREACH query failing

Question

Problem: Neo4j Browser locks on Executing query... or returns Unknown error. Cause is third FOREACH in my query below, only the FOREACH definition not it's MERGE/CREATE clauses.

I tried to make a Cypher query that would take a collection of years as integers and create year/month/day nodes. I attempted to reduce the lines by using collections/maps prior and then nested FOREACH clauses, which works fine until the FOREACH for creating days.

Problem seems to be from using a mapped integer, if I replace m.num with a plain integer it will process the days fine, but only for a single month(multiple times).

The query was based off of Kenny Bastani's query here:
https://gist.github.com/kbastani/8519557

I also changed CREATE to CREATE UNIQUE which should prevent duplicate relationships/nodes similar to MERGE?

Should I be writing the query differently? I did have an older version of the query work once but then lock on executing query, both on localhost and grapheneDB. When the initial test query(years:1999,2000,2004,2010,2400) worked on grapheneDB it returned:

Added 1755 labels, created 1755 nodes, set 5195 properties, created 1800 relationships, returned 0 rows in 20049 ms

Is that a reasonable amount of time? Would it be slow due to using GrapheneDB and it's 256mb RAM? Or by reducing the Cypher into nested queries and collection/maps was I making the query inefficient performance wise?

Here is my query:

//Date ranges in a month
WITH 
    range(1, 28) as Days_28, 
    range(1, 29) as Days_29, 
    range(1, 30) as Days_30, 
    range(1, 31) as Days_31

//Assign months date ranges
WITH 
    Days_31 as January, 
    Days_28 as February, 
    Days_29 as Leap_February, 
    Days_31 as March, 
    Days_30 as April, 
    Days_31 as May, 
    Days_30 as June,
    Days_31 as July, 
    Days_31 as August, 
    Days_30 as September, 
    Days_31 as October, 
    Days_30 as November, 
    Days_31 as December

//Mapping months to days keys - num key could be avoided by changing FOREACH to: 'FOREACH(m IN range(1,length(year.months)) |' or 'FOREACH(m IN range(1, 12) |'
WITH [
    {num: 1, days: January}, 
    {num: 2, days: February}, 
    {num: 3, days: March}, 
    {num: 4, days: April}, 
    {num: 5, days: May}, 
    {num: 6, days: June}, 
    {num: 7, days: July}, 
    {num: 8, days: August}, 
    {num: 9, days: September}, 
    {num: 10, days: October}, 
    {num: 11, days: November}, 
    {num: 12, days: December}
    ] as regular_year, Leap_February

//Create leap year
WITH [regular_year[0] , {num: 2, days: Leap_February} , regular_year[2..11]] AS leap_year, regular_year

//Years to create are stored in years collection - anyway to move this to the top without having to add it to the end of every WITH clause prior?
WITH [1996] as years,leap_year,regular_year
//Check if year is a leap year, if so map to leap_year, if not map regular_year
WITH [year IN years | CASE WHEN (year%4=0 AND NOT year%100=0) THEN {year:year, months:leap_year} WHEN (year%4=0 AND year%100=0 AND year%400=0) THEN {year:year, months:leap_year} ELSE {year:year, months:regular_year} END] AS yearMap

//Create nodes/relationships for years/months/days
FOREACH(year IN yearMap |
    MERGE (thisYear: Year {year: year.year})

    FOREACH(m IN year.months |
        MERGE (thisMonth :Month { month: m.num, year: year.year })
        CREATE UNIQUE (thisYear)-[:HAS_MONTH]->(thisMonth)

        MERGE (firstDay :Day { day: 1, month: m.num, year: year.year })
        CREATE UNIQUE (thisMonth)-[:FIRST]->(firstDay)

        //This FOREACH line is causing a problem, if replace m.num with an integer value it works, but then only processes that month
        FOREACH (d IN TAIL((year.months[m.num]).days) |
            MERGE (thisDay: Day { day: d, month: m.num, year: year.year })
            MERGE (lastDay: Day { day: d - 1, month: m.num, year: year.year })
            CREATE UNIQUE(lastDay)-[:NEXT]->(thisDay)
        )

    MERGE (lastDay: Day { day: last((year.months[m.num]).days), month: m.num, year: year.year })
    CREATE UNIQUE (thisMonth)-[:LAST]->(lastDay)
    )
)

Update: I've managed to spot some mistakes. Since my first version of the query I changed the way I create the leap_year collection. Regular years with the query work fine, it was just broken with leap years. This was because of misunderstanding regular_year[2..11], I was expecting it to slice [2]-[11], however the 2nd integer is a cap? Changing that to 12 adds all remaining months. The method also caused leap_year to have a length of 3 instead of 12, Have worked around it by changing the line to this:
WITH [regular_year[0], {num: 2, days: Leap_February}] + filter(month in regular_year WHERE month.num>2) AS leap_year, regular_year

The 2nd issue I picked up was in the third FOREACH I was using (year.months[m.num]).days Where I should have m.num-1 to access the collection correctly, and again for the last MERGE. The query is still broken for leap years however..

Update 2: I think my fixes listed in EDIT have done the trick. In my localhostDB all years seem to create fine except for the year I was testing (2000), I checked with a similar leap year (2400) and that worked.

I attempted the query on a database in grapheneDB and got the following error:
The pattern (thisMonth)-[1469:FIRST]->(firstDay) produced multiple possible paths, and that is not allowed

The snippet of code it is referring to is:

FOREACH(m IN year.months |
 MERGE (thisMonth:Month { month: m.num, year: year.year })
 CREATE UNIQUE (thisYear)-[:HAS_MONTH]->(thisMonth)

 MERGE (firstDay:Day { day: 1, month: m.num, year: year.year })
 CREATE UNIQUE (thisMonth)-[:FIRST]->(firstDay)

I had been working on this query in that database in grapheneDB, creating a new database and using the query everything worked fine, even year 2000. It took about 4000ms, I then tried the query with a mix of regular/leap years totalling 6 years. It was unable to complete the query, plenty of nodes existed though. Using the query again on a single year (existing or not) failed. My query must be breaking the database somehow?

Answer 1

I talked to the graphenedb guys:

One the free sandbox instances there are certain resource limits for memory, cpu and execution-time. And if one of the databases exceeds further limits and doesn't recover it will be restarted.

They also said that you should contact their support if you run into issues on graphenedb. Or if you use the heroku add-on go via the heroku support system.