cancel
Showing results for 
Search instead for 
Did you mean: 

Head's Up! Site migration is underway. Phase 1: replicate users.

A query with a subquery, the first one returning nothing

paolodipietro58
Graph Voyager

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?

19 REPLIES 19

glilienfield
Ninja
Ninja

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.

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
Ninja
Ninja

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.

The same: it always returns []!

glilienfield
Ninja
Ninja

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

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

glilienfield
Ninja
Ninja

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?

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!

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

glilienfield
Ninja
Ninja

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.

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

Cobra
Ninja
Ninja

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

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
Ninja
Ninja

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

I already have APOC!

Cobra
Ninja
Ninja

Do you use Neo4j Server or Neo4j Desktop?

I'm running neo4j community server 4.3.2 on Ubuntu 20.04.3

Cobra
Ninja
Ninja

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

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

paolodipietro58
Graph Voyager

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: []