CASE with python params fails

[bigluck]

Hi, I have an edge case, and I can't find an easy way to deal with it without having to run 2 separate queries.

I'm testing it on v0.3.3 dev1 (I can not test more recent versions because they cause segment faults in my code).

I have a node, and I want to set one of its params manually:

conn.execute(
    query=f"""
        MATCH
            (a:{NodeOne} {{ id: $a_id }}),
            (b:{NodeTwo} {{ id: $b_id }}),
            (c:{NodeOne} {{ id: $c_id }} )
        MERGE
            (a)-[:{RelA}]->(c),
            (b)-[:{RelB} {{ my_param: $my_param }}]->(c)
    """,
    params={
        'a_id': obj['a_id'],
        'b_id': obj['b_id'],
        'c_id': obj['c_id'],
        'my_param': obj['my_param'],
    },
)

It works, but when one of the params is None it raises this exception:
RuntimeError: Unknown parameter type <class 'NoneType'>

Then I tried using a CASE, but it fails

conn.execute(
    query=f"""
        MATCH
            (a:{NodeOne} {{ id: $a_id }}),
            (b:{NodeTwo} {{ id: $b_id }}),
            (c:{NodeOne} {{ id: $c_id }} )
        MERGE
            (a)-[:{RelA}]->(c),
            (b)-[:{RelB} {{ my_param: CASE $my_param WHEN '' THEN NULL ELSE $my_param END }}]->(c)
    """,
    params={
        'a_id': obj['a_id'],
        'b_id': obj['b_id'],
        'c_id': obj['c_id'],
        'my_param': obj['my_param'] or '',
    },
)

This is the new error I got: Binder exception: Data type of expression CASE WHEN EQUALS($my_param,) THEN ELSE $my_param should not be modified.

I tried this different approach:

conn.execute(
    query=f"""
        MATCH
            (a:{NodeOne} {{ id: $a_id }}),
            (b:{NodeTwo} {{ id: $b_id }}),
            (c:{NodeOne} {{ id: $c_id }} )
        WITH *, $my_param AS my_param
        MERGE
            (a)-[:{RelA}]->(c),
            (b)-[:{RelB} {{ my_param: CASE my_param WHEN '' THEN NULL ELSE my_param END }}]->(c)
    """,
    params={
        'a_id': obj['a_id'],
        'b_id': obj['b_id'],
        'c_id': obj['c_id'],
        'my_param': obj['my_param'] or '',
    },
)

but it still fails with: Binder exception: Data type of expression CASE WHEN EQUALS(my_param,) THEN ELSE my_param should not be modified.

Finally I also tested this:

conn.execute(
    query=f"""
        MATCH
            (a:{NodeOne} {{ id: $a_id }}),
            (b:{NodeTwo} {{ id: $b_id }}),
            (c:{NodeOne} {{ id: $c_id }} )
        WITH *, $my_param AS my_param
        MERGE
            (a)-[:{RelA}]->(c),
            (b)-[:{RelB} {{ my_param: CASE SIZE(my_param) WHEN 0 THEN NULL ELSE my_param END }}]->(c)
    """,
    params={
        'a_id': obj['a_id'],
        'b_id': obj['b_id'],
        'c_id': obj['c_id'],
        'my_param': obj['my_param'] or '',
    },
)

but now it fails with Binder exception: Data type of expression CASE WHEN EQUALS(SIZE(my_param),0) THEN ELSE my_param should not be modified.

It seems like null is misunderstood by the DB.

[acquamarin]

Hi,
#3098 should correctly handle null values in python.
Thanks for reporting to us!

[bigluck]

Thanks @acquamarin
Another open point of my issue is: how can I conditionally set a parameter to null using cypher?

[andyfengHKU]

Hi @bigluck,

How can I conditionally set a parameter to null using cypher?

I may not understand this question correctly. But I thought you can just set parameter at API level 'my_param': None or <some other value>. Can you elaborate more on the part of setting parameter to null using cypher?

[bigluck]

@andyfengHKU have a look at my merge statements, I’m trying using case with different conditions, but the queries fail

[bigluck]

@andyfengHKU just tested with the latest nightly build (v0.3.3.dev15) and this query works now:

MATCH ...
MERGE
    (a)-[:MyRel {
        my_col: CASE c.my_filter WHEN '' THEN NULL else c.my_filter END
    }]->(b)

I still need to test what happens when a python argument is used on the CASE

[andyfengHKU]

This should be fixed in #3140

[bigluck]

Thanks @andyfengHKU!