# A query with a subquery, the first one returning nothing

**URL:** <https://community.neo4j.com/t/a-query-with-a-subquery-the-first-one-returning-nothing/53914>\
**Category:** Procedures & APOC\
**Tags:** cypher\
**Created:** [March 25, 2022, 12:02pm UTC](https://community.neo4j.com/t/a-query-with-a-subquery-the-first-one-returning-nothing/53914 "2022-03-25T12:02:10Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![paolodipietro58](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/paolodipietro58/32/21045_2.png) [@paolodipietro58](https://community.neo4j.com/u/paolodipietro58)\
**Post date:** [March 25, 2022, 12:02pm UTC](https://community.neo4j.com/t/a-query-with-a-subquery-the-first-one-returning-nothing/53914/1 "2022-03-25T12:02:10Z")

</div>

I'm running neo4j community 4.3.2 on Ubuntu 20.04.3

I have the following query:

```auto
      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?

---

<div class="post-metadata">

**Author:** ![glilienfield](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/glilienfield/32/27534_2.png) [@glilienfield](https://community.neo4j.com/u/glilienfield)\
**Post date:** [March 25, 2022, 12:27pm UTC](https://community.neo4j.com/t/a-query-with-a-subquery-the-first-one-returning-nothing/53914/2 "2022-03-25T12:27:51Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![paolodipietro58](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/paolodipietro58/32/21045_2.png) [@paolodipietro58](https://community.neo4j.com/u/paolodipietro58)\
**Post date:** [March 25, 2022, 12:31pm UTC](https://community.neo4j.com/t/a-query-with-a-subquery-the-first-one-returning-nothing/53914/3 "2022-03-25T12:31:26Z")

</div>

It returns an error if I remove the return statement:

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

```

---

<div class="post-metadata">

**Author:** ![glilienfield](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/glilienfield/32/27534_2.png) [@glilienfield](https://community.neo4j.com/u/glilienfield)\
**Post date:** [March 25, 2022, 12:40pm UTC](https://community.neo4j.com/t/a-query-with-a-subquery-the-first-one-returning-nothing/53914/4 "2022-03-25T12:40:44Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![paolodipietro58](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/paolodipietro58/32/21045_2.png) [@paolodipietro58](https://community.neo4j.com/u/paolodipietro58)\
**Post date:** [March 25, 2022, 12:52pm UTC](https://community.neo4j.com/t/a-query-with-a-subquery-the-first-one-returning-nothing/53914/5 "2022-03-25T12:52:52Z")

</div>

The same: it always returns `[]`!

---

<div class="post-metadata">

**Author:** ![glilienfield](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/glilienfield/32/27534_2.png) [@glilienfield](https://community.neo4j.com/u/glilienfield)\
**Post date:** [March 25, 2022, 1:00pm UTC](https://community.neo4j.com/t/a-query-with-a-subquery-the-first-one-returning-nothing/53914/6 "2022-03-25T13:00:17Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![paolodipietro58](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/paolodipietro58/32/21045_2.png) [@paolodipietro58](https://community.neo4j.com/u/paolodipietro58)\
**Post date:** [March 25, 2022, 1:02pm UTC](https://community.neo4j.com/t/a-query-with-a-subquery-the-first-one-returning-nothing/53914/7 "2022-03-25T13:02:12Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![glilienfield](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/glilienfield/32/27534_2.png) [@glilienfield](https://community.neo4j.com/u/glilienfield)\
**Post date:** [March 25, 2022, 2:48pm UTC](https://community.neo4j.com/t/a-query-with-a-subquery-the-first-one-returning-nothing/53914/8 "2022-03-25T14:48:05Z")

</div>

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?

 ![Screen Shot 2022-03-25 at 10.43.06 AM](https://us1.discourse-cdn.com/flex021/uploads/neo4jcommunity/original/3X/f/f/ff32429aaa2d506c194e45e3824ac2067c0d360d.png)

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?

---

<div class="post-metadata">

**Author:** ![paolodipietro58](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/paolodipietro58/32/21045_2.png) [@paolodipietro58](https://community.neo4j.com/u/paolodipietro58)\
**Post date:** [March 25, 2022, 4:20pm UTC](https://community.neo4j.com/t/a-query-with-a-subquery-the-first-one-returning-nothing/53914/9 "2022-03-25T16:20:47Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![glilienfield](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/glilienfield/32/27534_2.png) [@glilienfield](https://community.neo4j.com/u/glilienfield)\
**Post date:** [March 25, 2022, 4:58pm UTC](https://community.neo4j.com/t/a-query-with-a-subquery-the-first-one-returning-nothing/53914/10 "2022-03-25T16:58:54Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![glilienfield](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/glilienfield/32/27534_2.png) [@glilienfield](https://community.neo4j.com/u/glilienfield)\
**Post date:** [March 25, 2022, 5:52pm UTC](https://community.neo4j.com/t/a-query-with-a-subquery-the-first-one-returning-nothing/53914/11 "2022-03-25T17:52:29Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![paolodipietro58](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/paolodipietro58/32/21045_2.png) [@paolodipietro58](https://community.neo4j.com/u/paolodipietro58)\
**Post date:** [March 25, 2022, 6:46pm UTC](https://community.neo4j.com/t/a-query-with-a-subquery-the-first-one-returning-nothing/53914/12 "2022-03-25T18:46:47Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Cobra](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/cobra/32/35243_2.png) [@Cobra](https://community.neo4j.com/u/Cobra)\
**Post date:** [March 26, 2022, 10:21am UTC](https://community.neo4j.com/t/a-query-with-a-subquery-the-first-one-returning-nothing/53914/13 "2022-03-26T10:21:30Z")

</div>

Hello @paolodipietro58 🙂

Could you try these two queries?

```auto
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:

```auto
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

---

<div class="post-metadata">

**Author:** ![paolodipietro58](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/paolodipietro58/32/21045_2.png) [@paolodipietro58](https://community.neo4j.com/u/paolodipietro58)\
**Post date:** [March 27, 2022, 5:19pm UTC](https://community.neo4j.com/t/a-query-with-a-subquery-the-first-one-returning-nothing/53914/14 "2022-03-27T17:19:34Z")

</div>

The first one returns an error in the subcall

```auto
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:

```auto
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!

---

<div class="post-metadata">

**Author:** ![Cobra](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/cobra/32/35243_2.png) [@Cobra](https://community.neo4j.com/u/Cobra)\
**Post date:** [March 27, 2022, 7:51pm UTC](https://community.neo4j.com/t/a-query-with-a-subquery-the-first-one-returning-nothing/53914/15 "2022-03-27T19:51:38Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![paolodipietro58](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/paolodipietro58/32/21045_2.png) [@paolodipietro58](https://community.neo4j.com/u/paolodipietro58)\
**Post date:** [March 27, 2022, 9:35pm UTC](https://community.neo4j.com/t/a-query-with-a-subquery-the-first-one-returning-nothing/53914/16 "2022-03-27T21:35:30Z")

</div>

I already have APOC!

---

<div class="post-metadata">

**Author:** ![Cobra](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/cobra/32/35243_2.png) [@Cobra](https://community.neo4j.com/u/Cobra)\
**Post date:** [March 28, 2022, 6:33am UTC](https://community.neo4j.com/t/a-query-with-a-subquery-the-first-one-returning-nothing/53914/17 "2022-03-28T06:33:07Z")

</div>

Do you use Neo4j Server or Neo4j Desktop?

---

<div class="post-metadata">

**Author:** ![paolodipietro58](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/paolodipietro58/32/21045_2.png) [@paolodipietro58](https://community.neo4j.com/u/paolodipietro58)\
**Post date:** [March 28, 2022, 8:41am UTC](https://community.neo4j.com/t/a-query-with-a-subquery-the-first-one-returning-nothing/53914/18 "2022-03-28T08:41:09Z")

</div>

I'm running neo4j community server 4.3.2 on Ubuntu 20.04.3

---

<div class="post-metadata">

**Author:** ![Cobra](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/cobra/32/35243_2.png) [@Cobra](https://community.neo4j.com/u/Cobra)\
**Post date:** [March 28, 2022, 8:56am UTC](https://community.neo4j.com/t/a-query-with-a-subquery-the-first-one-returning-nothing/53914/19 "2022-03-28T08:56:02Z")

</div>

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

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

```

---

<div class="post-metadata">

**Author:** ![paolodipietro58](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/paolodipietro58/32/21045_2.png) [@paolodipietro58](https://community.neo4j.com/u/paolodipietro58)\
**Post date:** [March 28, 2022, 9:29am UTC](https://community.neo4j.com/t/a-query-with-a-subquery-the-first-one-returning-nothing/53914/20 "2022-03-28T09:29:02Z")

</div>

I checked and it is strange:

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

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