ArcadeDB Query Issue: SELECT FROM ... WHERE ANY() LIKE (Unknown function name 'ANY')

[GabiPDev]

ArcadeDB Version: v23.11.1-SNAPSHOT (build 8c2ca2d/1700033725715/main)

OS and JDK Version:

Running on Linux 5.15.133.1-microsoft-standard-WSL2 - OpenJDK 64-Bit Server VM 11.0.21 (Temurin-11.0.21+9)

Expected behavior

The query SELECT FROM Scientist WHERE ANY() LIKE '%Physics%' should return all Scientist vertices where any field contains the specified word.

Actual behavior

Error: Unknown function name 'ANY'
<ArcadeDB_0> Error on command execution (PostCommandHandler)
arcadedb | com.arcadedb.exception.CommandExecutionException: Unknown function name 'ANY'
arcadedb | at com.arcadedb.query.sql.SQLQueryEngine.getFunction(SQLQueryEngine.java:198)
arcadedb | at com.arcadedb.query.sql.parser.FunctionCall.getFunction(FunctionCall.java:418)
arcadedb | at com.arcadedb.query.sql.parser.FunctionCall.getCachedFunction(FunctionCall.java:423)
arcadedb | at com.arcadedb.query.sql.parser.FunctionCall.isIndexedFunctionCall(FunctionCall.java:144)
arcadedb | at com.arcadedb.query.sql.parser.LevelZeroIdentifier.isIndexedFunctionCall(LevelZeroIdentifier.java:78)
arcadedb | at com.arcadedb.query.sql.parser.BaseIdentifier.isIndexedFunctionCall(BaseIdentifier.java:74)
arcadedb | at com.arcadedb.query.sql.parser.BaseExpression.isIndexedFunctionCall(BaseExpression.java:163)
arcadedb | at com.arcadedb.query.sql.parser.Expression.isIndexedFunctionCal(Expression.java:206)
arcadedb | at com.arcadedb.query.sql.parser.BinaryCondition.getIndexedFunctionConditions(BinaryCondition.java:108)
arcadedb | at com.arcadedb.query.sql.parser.AndBlock.getIndexedFunctionConditions(AndBlock.java:103)
arcadedb | at com.arcadedb.query.sql.executor.SelectExecutionPlanner.handleClassAsTargetWithIndexedFunction(SelectExecutionPlanner.java:1304)
arcadedb | at com.arcadedb.query.sql.executor.SelectExecutionPlanner.handleClassAsTarget(SelectExecutionPlanner.java:1255)
arcadedb | at com.arcadedb.query.sql.executor.SelectExecutionPlanner.handleClassAsTarget(SelectExecutionPlanner.java:1249)
arcadedb | at com.arcadedb.query.sql.executor.SelectExecutionPlanner.handleFetchFromTarget(SelectExecutionPlanner.java:782)
arcadedb | at com.arcadedb.query.sql.executor.SelectExecutionPlanner.createExecutionPlan(SelectExecutionPlanner.java:144)
arcadedb | at com.arcadedb.query.sql.parser.SelectStatement.createExecutionPlan(SelectStatement.java:212)
arcadedb | at com.arcadedb.query.sql.parser.SelectStatement.execute(SelectStatement.java:206)
arcadedb | at com.arcadedb.query.sql.parser.Statement.execute(Statement.java:73)
arcadedb | at com.arcadedb.query.sql.SQLQueryEngine.command(SQLQueryEngine.java:101)
arcadedb | at com.arcadedb.database.EmbeddedDatabase.command(EmbeddedDatabase.java:1330)
arcadedb | at com.arcadedb.server.ServerDatabase.command(ServerDatabase.java:467)
arcadedb | at com.arcadedb.server.http.handler.PostCommandHandler.executeCommand(PostCommandHandler.java:129)
arcadedb | at com.arcadedb.server.http.handler.PostCommandHandler.execute(PostCommandHandler.java:96)
arcadedb | at com.arcadedb.server.http.handler.DatabaseAbstractHandler.execute(DatabaseAbstractHandler.java:99)
arcadedb | at com.arcadedb.server.http.handler.AbstractServerHttpHandler.handleRequest(AbstractServerHttpHandler.java:127)
arcadedb | at io.undertow.server.Connectors.executeRootHandler(Connectors.java:393)
arcadedb | at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:859)
arcadedb | at org.jboss.threads.ContextHandler$1.runWith(ContextHandler.java:18)
arcadedb | at org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2513)
arcadedb | at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1538)
arcadedb | at org.xnio.XnioWorker$WorkerThreadFactory$1$1.run(XnioWorker.java:1282)
arcadedb | at java.base/java.lang.Thread.run(Thread.java:829)

Steps to reproduce

Create a new database that contains vertices of type 'Scientist'.
Insert some sample data into the 'Scientist' vertices, ensuring that various fields contain diverse words for testing.
Run the query SELECT FROM Scientist WHERE ANY() LIKE '%Physics%'
Observe the behavior or results, noting if they differ from the expected outcome where all Scientist vertices containing the specified word in any field should be returned.

[lvca]

We didn't port the any() modifier from OrientDB, where it was available, together with all(). I can see we have any() in the documentation (that part was forked from OrientDB). @gramian WDYT? Should we just implement any() or remove it from the docs?

[gramian]

Hmm, I am using neither any() not all() so I don't have a personal need. That said, I assume one would use it in a schema-less DB or with semi-structured data. So I can imagine use-cases.
Since nobody filed an issue about any() not working till now, I guess it is not really in use a lot practically, so I would remove it.
What about all()?

[lvca]

all() can be useful to test a condition against all the properties something like select from Product where all() is not null. I'm happy with removing any and all from our docs until we find a real interest from users to support them.

[gramian]

@GabiPDev To address your problem: you can search all values of a type using @this.values(), for example:

SELECT FROM Scientist WHERE @this.values().asString() ILIKE '%Physics%'

I hope this is a viable alternative for you.

[lvca]

Awesome, it would have never crossed my mind!

[GabiPDev]

Thank you, @gramian, for the alternative approach using @this.values(). This seems like a practical solution for searching values within a type, and the example you provided is very helpful. I appreciate the quick response @lvca. I'll try this method in my use case.