-1004:SyntaxError: Where clause in optional match is not supported. near `kyquyen_congdong|e_dangkyquyen'. How to alternate OPTIONAL MATCH with WHERE in version 3.4 ?

[son2408]

This' s my graph.

MATCH(v1:dangkyquyen) -[e1:e_dangkyquyen_tinhhinhdangky|e_dangkyquyen_vochong|e_dangkyquyen_hogiadinh]-> (v2) WHERE v1.dangkyquyen.NodeId IN ['de9b68d1-6327-4c38-b689-d2a027ed2a96']
OPTIONAL MATCH(v2) -[e2:e_hogiadinh_canhan|e_vochong_canhan|e_tochuc_canhan|e_tinhhinhdangky_canhan]-> (v3) WHERE v3.canhan.isLastest == 1
return v1.dangkyquyen.NodeId,v2.tinhhinhdangky.NodeId,v2.vochong.NodeId,v2.hogiadinh.NodeId,v3.canhan.NodeId

[Sophie-Xie]

This issue is the same as #5362.

[son2408]

@Sophie-Xie this use-case, I still want to return v1, v2 although v3 is null. Instead of use option match, what's other way?

[son2408]

@wey-gu

[xtcyclist]

MATCH (v1:dangkyquyen) -[e1:e_dangkyquyen_tinhhinhdangky|e_dangkyquyen_vochong|e_dangkyquyen_hogiadinh]->(v2)-[e2:e_hogiadinh_canhan|e_vochong_canhan|e_tochuc_canhan|e_tinhhinhdangky_canhan]->(v3) WHERE v1.dangkyquyen.NodeId IN ['de9b68d1-6327-4c38-b689-d2a027ed2a96'] and v3.canhan.isLastest == 1 \
return v1.dangkyquyen.NodeId,v2.tinhhinhdangky.NodeId,v2.vochong.NodeId,v2.hogiadinh.NodeId,v3.canhan.NodeId \
UNION \
MATCH(v1:dangkyquyen) -[e1:e_dangkyquyen_tinhhinhdangky|e_dangkyquyen_vochong|e_dangkyquyen_hogiadinh]->(v2) WHERE v1.dangkyquyen.NodeId IN ['de9b68d1-6327-4c38-b689-d2a027ed2a96'] \
return v1.dangkyquyen.NodeId,v2.tinhhinhdangky.NodeId,v2.vochong.NodeId,v2.hogiadinh.NodeId,null

Maybe something like this. Not equivalent though.

[son2408]

MATCH (v1:dangkyquyen) -[e1:e_dangkyquyen_tinhhinhdangky|e_dangkyquyen_vochong|e_dangkyquyen_hogiadinh]->(v2)-[e2:e_hogiadinh_canhan|e_vochong_canhan|e_tochuc_canhan|e_tinhhinhdangky_canhan]->(v3) WHERE v1.dangkyquyen.NodeId IN ['de9b68d1-6327-4c38-b689-d2a027ed2a96'] and v3.canhan.isLastest == 1 \
return v1.dangkyquyen.NodeId,v2.tinhhinhdangky.NodeId,v2.vochong.NodeId,v2.hogiadinh.NodeId,v3.canhan.NodeId \
UNION \
MATCH(v1:dangkyquyen) -[e1:e_dangkyquyen_tinhhinhdangky|e_dangkyquyen_vochong|e_dangkyquyen_hogiadinh]->(v2) WHERE v1.dangkyquyen.NodeId IN ['de9b68d1-6327-4c38-b689-d2a027ed2a96'] \
return v1.dangkyquyen.NodeId,v2.tinhhinhdangky.NodeId,v2.vochong.NodeId,v2.hogiadinh.NodeId,null

Maybe something like this. Not equivalent though.

If I have multi MATCHES with return different results then can't use UNION
Example:
prop(v1): a1,b1,c1..
prop(v2): a2,b2,c2..
prop(v3): a3,b3,c3..
prop(v4): a4,b4,c4..
Graph:

MATCH(v1) -[e:e_12] ->(v2)
MATCH(v2) -[e:e_23] ->(v3)
MATCH(v3) -[e:e_34] ->(v4)

Data

v1 | v2 | v3 | v4
a1 | a2 | null | null

if I graph MATCH(v1) -[e:e_12]-> (v2)-[e:e_23]-> (v3)-[e:e_34] ->(v4) then Nebula return no result

v1 | v2 | v3 | v4

but I expect that

v1 | v2 | v3 | v4
a1 | a2 | null | null

With version 3.3 I use Optional match @xtcyclist, @wey-gu

[xtcyclist]

In 3.3, we have discovered that the OPTINAL MATCH with WHERE produces wrong results. And, it takes too much (refactoring a very large porition of the code base) to fix it. We tried to find an affordable solution but didn't find one. In the end, we have to ban the syntax in 3.4.

With this syntax banned, there is no guarantee that an OPTIONAL MATCH with WHERE could be rewritten into some other queries and be executed correctly.

For your case:

MATCH(v1) -[e:e_12] ->(v2)
MATCH(v2) -[e:e_23] ->(v3)
MATCH(v3) -[e:e_34] ->(v4)

I recommend you to split it into multiple queries and merge the results in your own app, where you could fill in NULL values when needed.

[QingZ11]

I have noticed that the issue you created hasn’t been updated for nearly a month, so I have to close it for now. If you have any new updates, you are welcome to reopen this issue anytime.

Thanks a lot for your contribution anyway 😊