A query with a subquery, the first one returning nothing

[paolodipietro58]

I'm running neo4j community 4.3.2 on Ubuntu 20.04.3

I have the following query:

      MATCH (product:Product {uuid: 'e418f3cd-c26e-483d-9915-acdb0408af79' })  
      MATCH (user:User {userID: '10227287152944531'})
      MERGE (user)-[:HAS_CART]->(cart:Cart {uuid: 1})

      MERGE (cart)-[contains:CONTAINS]->(product)
        ON CREATE
            SET contains.quantity = 1
        ON MATCH
            SET contains.quantity = contains.quantity + 1

      WITH contains, contains.quantity as quantity 
      
        call {
              WITH contains 
        
              WITH contains 
                WHERE contains.quantity = 0
                  DETACH DELETE contains

              RETURN contains as nil
        }
        
      return quantity

This query perform exactly as expected: it creates the missing nodes and set the value of the contains.quantity property.

The problem is the return value: it is [] while I expect a quantity.

If I remove the subquery, the return value is correct.

Any suggestion please?

[glilienfield]

I think it is due to your return statement in the sub query. When joined back to the outer query results, it is producing no rows since it is overwriting contains with nil. Try removing the return. It is not serving a purpose in the outer query anyway and a return statement in the sub query is not required.

[paolodipietro58]

It returns an error if I remove the return statement:

CALL subquery cannot conclude with DELETE (must be RETURN) (line 21, column 26 (offset: 721))
"                  DETACH DELETE contains"
                          ^

[glilienfield]

Interesting. I will check that out. In the meantime, as a work around try

Return 1 as x

The value of x should be ignore and your quantity value returned.

[paolodipietro58]

The same: it always returns []!

[glilienfield]

Ok. Maybe deleting contains, removes the entire row from your result set. I will have to play with it then. Sorry.

[paolodipietro58]

Why sorry? Thank you!
Anyway, it's intriguing!

[glilienfield]

It is intriguing. cypher issues are fun to figure out.

I ran your query in my browser. I got the same behavior with a return statement. I think what is happening is that the WHERE clause is not true, so the subquery's result is null. The null value is returned and joined to the outer query's result, resulting in a null result. You can see that a quantity value is returned when the condition is changed to be true, such as quantity > 0.

I also executed it with the subquery's RETURN being commented out, and it ran and returned the quantity. Not sure why we are getting difference behavior. I am using neo4j 4.4.4. Is yours the same?

I do have follow up question. How is the subquery condition every going to be true, since the value of quantity seems to have to be a positive value greater than zero. Could you have the 'delete product from cart' operation in a separate query called when the quantity is known to be zero?

[paolodipietro58]

I'm running the 4.3.2, and maybe this could explain the difference.

Well, in the real world the number could also go down to 0, so yes, I have already splitted the query in two parts, the second executing only when the quantity is = 0. And this solved the problem.

But in 4.3.2 the return statement looks mandatory.

We'll expect for a follow up, if any. This is why I suggested Cypher people to add some direct (easy) if-then-else or switch statement to the cypher language: the WITH alone is not enough!

Anyway, thank you!

[glilienfield]

I totally agree, an if-else construct would be fantastic. Maybe a WHERE IF / ELSE following a WITH statement.

[glilienfield]

I have a project laying around that has 4.2.3, so I tried the query in it. It gave me the "return is mandatory" error.

[paolodipietro58]

Please, signal this as it could be cause of error ( unattended results) while migrating from 4.3 to 4.4!

[Cobra]

Hello @paolodipietro58

Could you try these two queries?

MATCH (product:Product {uuid: 'e418f3cd-c26e-483d-9915-acdb0408af79' })  
MATCH (user:User {userID: '10227287152944531'})
MERGE (user)-[:HAS_CART]->(cart:Cart {uuid: 1})
MERGE (cart)-[contains:CONTAINS]->(product)
    ON CREATE
        SET contains.quantity = 1
    ON MATCH
        SET contains.quantity = contains.quantity + 1
WITH contains, contains.quantity AS quantity 
CALL {
    WITH contains 
    WHERE contains.quantity = 0
    DELETE contains
    RETURN contains AS nil
}
WITH quantity, nil
RETURN quantity

You can also use APOC for this case:

MATCH (product:Product {uuid: 'e418f3cd-c26e-483d-9915-acdb0408af79' })  
MATCH (user:User {userID: '10227287152944531'})
MERGE (user)-[:HAS_CART]->(cart:Cart {uuid: 1})
MERGE (cart)-[contains:CONTAINS]->(product)
    ON CREATE
        SET contains.quantity = 1
    ON MATCH
        SET contains.quantity = contains.quantity + 1
WITH contains, contains.quantity AS quantity 
CALL apoc.do.when(
  quantity = 0,
  'DELETE contains',
  '',
  {contains:contains})
YIELD value
RETURN quantity

Regards,
Cobra

[paolodipietro58]

The first one returns an error in the subcall

Importing WITH should consist only of simple references to outside variables. WHERE is not allowed. (line 12, column 5 (offset: 390))
"    WITH contains"
     ^

The second returns the following error:

There is no procedure with the name `apoc.do.when` registered for this database instance. Please ensure you've spelled the procedure name correctly and that the procedure is properly deployed.

Maybe the latter depends on the version? I'm using the 4.3.2!

[Cobra]

For the second one, you must install the plugin APOC on your database.

[paolodipietro58]

I already have APOC!

[Cobra]

Do you use Neo4j Server or Neo4j Desktop?

[paolodipietro58]

I'm running neo4j community server 4.3.2 on Ubuntu 20.04.3

[Cobra]

Can you make sure to have this in your neo4j.conf file:

dbms.security.procedures.unrestricted=apoc.*
dbms.security.procedures.whitelist=apoc.*

[paolodipietro58]

I checked and it is strange:

I had dbms.security.procedures.unrestricted=gds.* but I used some apoc procedures without problems:

apoc.create.uuid();, 
call apoc.create.setLabels

Now I changed it with dbms.security.procedures.unrestricted=gds.*,apoc.*, restarted the DB, but the result is always the same: []