The apoc.custom.declareProcedure throws exception "Query results do not match requested output" with some queries

[vga91]

How to Reproduce the Problem

See https://community.neo4j.com/t/apoc-custom-declareprocedure-always-throws-exception-corresponding-cypher-code-works/52004

Versions

• OS: Mac OSX
• Neo4j: 4.3
• Neo4j-Apoc: 4.3.0.5

[ralphavalon]

Hi,

After upgrading from APOC 4.4.0.3 to APOC 4.4.0.6, I started to see this same error.

Here's how to reproduce it:

version: '3'
services:
  neo4j:
    image: neo4j:4.4.7
    hostname: neo4j
    ports:
      - "7474:7474"
      - "7687:7687"
    environment:
      - NEO4J_AUTH=neo4j/test
      - NEO4J_dbms_logs_debug_level=DEBUG
      - NEO4JLABS_PLUGINS=["apoc"]
      - NEO4J_dbms_security_procedures_whitelist=apoc.*
      - NEO4J_dbms_security_procedures_unrestricted=apoc.*

Then, just login into http://localhost:7474/browser and run:

CALL apoc.custom.declareProcedure('exampleTest(exampleId::STRING) ::(value::STRING)',
'MATCH (:ExampleNode)
 OPTIONAL MATCH (o:OtherExampleNode {identifier:$exampleId})
 RETURN o.identifier as value
 UNION ALL
 MATCH (n:ExampleNode)
 OPTIONAL MATCH (o:OtherExampleNode {identifier:$exampleId})
 RETURN o.identifier as value
');

It was tested with:

Neo4j: 4.4.7
APOC: 4.4.0.6

Obviously, the procedure is just an example, since the original one was way larger and with a lot of WHERE conditions, so I removed everything I could until I have a minimal reproducible example.

[psygnoser]

Here's an even simpler example of two variants of basically the same procedure declaration, of which one works and the other one doesn't:

Variant 0:

 CALL apoc.custom.declareProcedure(
    "test() :: (abc::INT)",
    "
    call {
        with 1 as abc return abc
        union all
        with 2 as abc return abc
    }
    return abc
    "
);

RESULT: Failure - Failed to invoke procedure apoc.custom.declareProcedure: Caused by: java.lang.RuntimeException: Query results do not match requested output.

Variant 1:

 CALL apoc.custom.declareProcedure(
    "test() :: (abc::INT)",
    "
    call {
        with 1 as a return a
        union all
        with 2 as a return a
    }
    return a as abc
    "
);

RESULT: Success

Tested with:
Neo4j: 4.4.7
APOC: 4.4.0.4, 4.4.0.7

Note that both of the examples above work normally with APOC: 4.4.0.3

[hlvtc]

We are not able to upgrade from APOC 4.4.0.3 due to the reported issue. Creation of some of our custom procedures fails . Steps to reproduce were described by @ralphavalon above:

Here's how to reproduce it:

version: '3'
services:
  neo4j:
    image: neo4j:4.4.10
    hostname: neo4j
    ports:
      - "7474:7474"
      - "7687:7687"
    environment:
      - NEO4J_AUTH=neo4j/test
      - NEO4J_dbms_logs_debug_level=DEBUG
      - NEO4JLABS_PLUGINS=["apoc"]
      - NEO4J_dbms_security_procedures_whitelist=apoc.*
      - NEO4J_dbms_security_procedures_unrestricted=apoc.*

Then, just login into http://localhost:7474/browser and run:

CALL apoc.custom.declareProcedure('exampleTest(exampleId::STRING) ::(value::STRING)',
'MATCH (:ExampleNode)
 OPTIONAL MATCH (o:OtherExampleNode {identifier:$exampleId})
 RETURN o.identifier as value
 UNION ALL
 MATCH (n:ExampleNode)
 OPTIONAL MATCH (o:OtherExampleNode {identifier:$exampleId})
 RETURN o.identifier as value
');

The result:

Failed to invoke procedure `apoc.custom.declareProcedure`: Caused by: java.lang.RuntimeException: Query results do not match requested output.

Last tested with:

Neo4j: 4.4.10
APOC: 4.4.0.8