SQL: UPDATE not triggering schema default value

[gramian]

ArcadeDB Version:

ArcadeDB Server v24.10.1 (build ac46a0da6ebe4f5d337e50874f3d8a0d1bf092b1/1728751149872/main)

OS and JDK Version:

Running on Linux 6.2.0-26-generic - OpenJDK 64-Bit Server VM 17.0.12

Given a type having a mandatory, notnull property with a default value, where a record is updated with the property in question being empty. Then the default value is not triggered and thus notnull and mandatory attributes trigger an error.

Expected behavior

Update existing record with default value

Actual behavior

Error on command execution (PostCommandHandler)
com.arcadedb.exception.ValidationException: The property 'doc.prop' is mandatory, but not found on record: #1:0@doc[]
	at com.arcadedb.database.DocumentValidator.throwValidationException(DocumentValidator.java:389)
	at com.arcadedb.database.DocumentValidator.validateField(DocumentValidator.java:43)
	at com.arcadedb.database.DocumentValidator.validate(DocumentValidator.java:38)
	at com.arcadedb.database.MutableDocument.validate(MutableDocument.java:134)
	at com.arcadedb.database.LocalDatabase.updateRecord(LocalDatabase.java:883)
	at com.arcadedb.database.MutableDocument.save(MutableDocument.java:297)
	at com.arcadedb.query.sql.executor.SaveElementStep$1.next(SaveElementStep.java:59)
	at com.arcadedb.query.sql.executor.CountStep.syncPull(CountStep.java:67)
	at com.arcadedb.query.sql.executor.SelectExecutionPlan.fetchNext(SelectExecutionPlan.java:44)
	at com.arcadedb.query.sql.executor.UpdateExecutionPlan.executeInternal(UpdateExecutionPlan.java:64)
	at com.arcadedb.query.sql.parser.UpdateStatement.execute(UpdateStatement.java:126)
	at com.arcadedb.query.sql.parser.Statement.execute(Statement.java:73)
	at com.arcadedb.query.sql.SQLQueryEngine.command(SQLQueryEngine.java:107)
	at com.arcadedb.database.LocalDatabase.command(LocalDatabase.java:1348)
	at com.arcadedb.server.ServerDatabase.command(ServerDatabase.java:472)
	at com.arcadedb.server.http.handler.PostCommandHandler.executeCommand(PostCommandHandler.java:134)
	at com.arcadedb.server.http.handler.PostCommandHandler.execute(PostCommandHandler.java:113)
	at com.arcadedb.server.http.handler.DatabaseAbstractHandler.execute(DatabaseAbstractHandler.java:100)
	at com.arcadedb.server.http.handler.AbstractServerHttpHandler.handleRequest(AbstractServerHttpHandler.java:127)
	at io.undertow.server.Connectors.executeRootHandler(Connectors.java:395)
	at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:859)
	at org.jboss.threads.ContextHandler$1.runWith(ContextHandler.java:18)
	at org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2513)
	at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1538)
	at org.xnio.XnioWorker$WorkerThreadFactory$1$1.run(XnioWorker.java:1282)
	at java.base/java.lang.Thread.run(Thread.java:840)

Steps to reproduce

CREATE DOCUMENT TYPE doc;
CREATE PROPERTY doc.prop STRING (mandatory true, notnull true, default 'Hi');
INSERT INTO doc CONTENT { "prop": "Ho" };
UPDATE doc CONTENT { } WHERE prop = 'Ho';

[gramian]

@lvca Could the solution to this issue be adding a procedure call to setDefaultValues(record) to this line: https://github.com/ArcadeData/arcadedb/blob/main/engine/src/main/java/com/arcadedb/database/LocalDatabase.java#L880 (compare https://github.com/ArcadeData/arcadedb/blob/main/engine/src/main/java/com/arcadedb/database/LocalDatabase.java#L820 )?

[lvca]

The default values were meant to be set only at creation, so the record has an initial state. it should be possible to update the record changing the default initial value, so the default shouldn't be triggered at every update.

What's your use case for this?

[gramian]

I have records where a timestamp is set via default sysdate('YYYY-MM-DD HH:MM:SS'). Now when the record gets updated meaning overwritten the timestamp needs to be renewed. I was expecting that when I overwrite the record in particular with UPDATE ... CONTENT ... that if the timestamp property is not provided, the default value will be used, thus a new timestamp is set. This was not the case, hence this issue.

[gramian]

If setting a default value on every update is a problem, then I guess it should at least happen for mandatory properties otherwise the error described above can still happen. WDYT?

[lvca]

I'm not sure we should update a property after the creation just because was set to null. I see Postgres has this behavior:

Oracle the same (without the RESET keyword)

[lvca]

Asked to an AI the same question for Neo4j and MongoDB: they all set default values at creation, never on update.

[gramian]

@lvca I see. Then please close my proposed pull request. Two follow questions arise for me then:

1. How should the error starting this issue be handled, meaning an UPDATE removes a mandatory field` Is an error then just fine here?
2. Is there another way to automatically set timestamps on updates without using default? Probably not via SQL as there are no triggers? Would something like an onupdate attribute make sense to you?

[lvca]

1. if the update removes a mandatory property, it should raise an error. That property should be set.
2. the trigger would work in this case

Another approach would be implementing the same behaviour of Postgres and Oracle on update. Something like:

UPDATE ... CONTENT ... APPLY DEFAULTS

In this case we could invoke the setDefaults() methods. WDYT?

[gramian]

APPLY DEFAULTS is a cool idea. That would solve my problem in a non-insular fashion.