SQL: support for `break` statement in SQL script foreach loops

[finduspedersen]

ArcadeDB Version:

24.5.1

OS and JDK Version:

Official Docker image

Expected behavior

The script should return with the result "Return statement 1"

Since ArcadeDB does not have a BREAK statement to discontinue the execution of a FOREACH loop, using RETURN could have been the natural alternative if one must handle errors gracefully. This is not possible, though :-(

Actual behavior

The script fails with this error:

2024-07-07 22:29:55 <ArcadeDB_0> Error on command execution (PostCommandHandler)
2024-07-07 22:29:55 java.util.NoSuchElementException
2024-07-07 22:29:55     at com.arcadedb.query.sql.executor.ScriptLineStep.executeUntilReturn(ScriptLineStep.java:114)
2024-07-07 22:29:55     at com.arcadedb.query.sql.executor.ScriptExecutionPlan.executeUntilReturn(ScriptExecutionPlan.java:182)
2024-07-07 22:29:55     at com.arcadedb.query.sql.executor.ScriptExecutionPlan.doExecute(ScriptExecutionPlan.java:92)
2024-07-07 22:29:55     at com.arcadedb.query.sql.executor.ScriptExecutionPlan.fetchNext(ScriptExecutionPlan.java:61)
2024-07-07 22:29:55     at com.arcadedb.query.sql.parser.LocalResultSet.fetchNext(LocalResultSet.java:44)
2024-07-07 22:29:55     at com.arcadedb.query.sql.parser.LocalResultSet.<init>(LocalResultSet.java:40)
2024-07-07 22:29:55     at com.arcadedb.query.sql.SQLScriptQueryEngine.executeInternal(SQLScriptQueryEngine.java:218)
2024-07-07 22:29:55     at com.arcadedb.query.sql.SQLScriptQueryEngine.command(SQLScriptQueryEngine.java:108)
2024-07-07 22:29:55     at com.arcadedb.database.LocalDatabase.command(LocalDatabase.java:1348)
2024-07-07 22:29:55     at com.arcadedb.server.ServerDatabase.command(ServerDatabase.java:472)
2024-07-07 22:29:55     at com.arcadedb.server.http.handler.PostCommandHandler.executeCommand(PostCommandHandler.java:134)
2024-07-07 22:29:55     at com.arcadedb.server.http.handler.PostCommandHandler.execute(PostCommandHandler.java:113)
2024-07-07 22:29:55     at com.arcadedb.server.http.handler.DatabaseAbstractHandler.execute(DatabaseAbstractHandler.java:100)
2024-07-07 22:29:55     at com.arcadedb.server.http.handler.AbstractServerHttpHandler.handleRequest(AbstractServerHttpHandler.java:127)
2024-07-07 22:29:55     at io.undertow.server.Connectors.executeRootHandler(Connectors.java:393)
2024-07-07 22:29:55     at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:859)
2024-07-07 22:29:55     at org.jboss.threads.ContextHandler$1.runWith(ContextHandler.java:18)
2024-07-07 22:29:55     at org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2513)
2024-07-07 22:29:55     at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1538)
2024-07-07 22:29:55     at org.xnio.XnioWorker$WorkerThreadFactory$1$1.run(XnioWorker.java:1282)
2024-07-07 22:29:55     at java.base/java.lang.Thread.run(Thread.java:829)

Steps to reproduce

FOREACH ($i IN [1, 2, 3]) {
    RETURN "Return statement 1";
}

RETURN "Return statement 2";

[lvca]

Reproduced.

[lvca]

I think adding a BREAK statement is a great idea. I am working on it.

[lvca]

Implemented. @gramian do you have time to add the new break statement in the documentation?