Cypher `MERGE` should use merge key with unique index

[LeonardoMarrancone]

ArcadeDB Version:

23.10.1

OS and JDK Version:

docker image

Expected behavior

Cypher MERGE should use merge key with unique index

Actual behavior

Cypher MERGE should use merge key with unique index, please look at the steps to reproduce the problem.

Steps to reproduce

CREATE VERTEX TYPE Test;
CREATE PROPERTY Test.id STRING;
CREATE INDEX ON Test (id) UNIQUE;
CREATE (v:Test{id: 0});
UNWIND range(0, 10) AS id MERGE (v:Test{id: id}) RETURN v; // returns: Found duplicate key in index Duplicated key [0] found on index 'Test[id]' already assigned to record #1:0

[lvca]

Reproduced.

[lvca]

This issue happens inside the Cypher to Gremlin converter. The generated gremlin for a simple MERGE query is not very readable. Also, it's hard to fix that component.

What about using SQL for that? You could do the same using a SQL UPDATE ... UPSERT ...

[LeonardoMarrancone]

Unfortunately we cannot move to SQL, we have to stick with Cypher, we're using a tool that works only with it. Could you please elaborate what the problem is? I mean what is the expected gremlin query vs the actual? Thank you so much!

[lvca]

This is the compiled query to Gremlin:

g.inject('  cypher.start').repeat(__.sideEffect(__.loops().aggregate('  GENERATED1'))).until(__.loops().is(gt(10))).select('  GENERATED1').unfold().as('id').choose(__.V().as('v').hasLabel('Test').where(__.select('v').values('id').where(eq('id'))), __.V().as('v').hasLabel('Test').where(__.select('v').values('id').where(eq('id'))), __.identity().addV('Test').as('v').property(single, 'id', __.select('id'))).select('v').project('v').by(__.valueMap().with('~tinkerpop.valueMap.tokens'))

[LeonardoMarrancone]

My Gremlin Knowledge is not so advanced so please forgive me if I'm saying something wrong.
Checking the generated query looks fine because (for sure is not optimized I agree):

g.inject('  cypher.start')
	.repeat(__.sideEffect(__.loops().aggregate('  GENERATED1')))
	.until(__.loops().is(gt(10)))
	.select('  GENERATED1')
	.unfold()
	.as('id')
	.choose(
		__.V().as('v').hasLabel('Test').where(__.select('v').values('id').where(eq('id'))),
        __.V().as('v').hasLabel('Test').where(__.select('v').values('id').where(eq('id'))),
		__.identity().addV('Test').as('v').property(single, 'id', __.select('id'))
	)
	.select('v')
	.project('v')
	.by(__.valueMap().with('~tinkerpop.valueMap.tokens'))

in the choose step it checks before if the node exists if true gets it, otherwise it creates a new one.
Do you see any issue in there?

[lvca]

I'm not a Gremlin expert. Tried to check in debug and it calls the addV() with id = 0 so the choose doesn't work for some reason.

[LeonardoMarrancone]

After a debug session we realised that there is no real problem in fact we declare Test.id as STRING and then we use an integer; changing it to CREATE PROPERTY Test.id INTEGER; make everything work as expected. Btw we would expect a type error with the first create, and that lead us to the problem; maybe is good to have a type check if it's defined.

[lvca]

You're right, the loookup on the index must always fail when insert the vertex with Id 0.

This is a bug and shouldn't happen. We support automatic cast between types when they are compatible., but seems like a piece is missing here.

Either we raise an error because the type wasn't a string or we should transform the value to string during the index lookup.

[lvca]

I can't drop the automatic conversion, otherwise in production systems would break after the upgrade. And honestly I think it's nice to have in place.

By debugging the original test case and the issue is not in the ArcadeDB part, but Gremlin: the comparison during the traversal (org.apache.tinkerpop.gremlin.process.traversal.Compare.eq()) fails because the automatic conversion is not supported by gremlin and the string "0" is not equals to 0.

Looking for a solution.