SQL: Field access for sub-query in projection

[gramian]

ArcadeDB Version: v23.1.2 (build 9dbe3ce/1675373316135/main)

JDK Version: openjdk 11.0.18 2023-01-17

OS: MacOS 12.6

Accessing a field from a sub-query in a projection, like:

SELECT (SELECT name FROM doc).name;

produces an error, while when wrapping the error in a function it works (see below).

Expected behavior

["test"]

Actual behavior

Cannot execute command Encountered " <SELECT> "SELECT "" at line 1, column 1.
Was expecting one of: <WHILE> ... <IF> ... <FOREACH> ... ";" ...

Details

Error on command execution (PostCommandHandler)
com.arcadedb.exception.CommandSQLParsingException: com.arcadedb.query.sql.parser.ParseException: Encountered " <SELECT> "SELECT "" at line 1, column 1.
Was expecting one of:
    <WHILE> ...
    <IF> ...
    <FOREACH> ...
    ";" ...
    
	at com.arcadedb.query.sql.SQLQueryEngine.parseScript(SQLQueryEngine.java:220)
	at com.arcadedb.query.sql.SQLQueryEngine.parseScript(SQLQueryEngine.java:212)
	at com.arcadedb.database.EmbeddedDatabase.execute(EmbeddedDatabase.java:1264)
	at com.arcadedb.server.ServerDatabase.execute(ServerDatabase.java:421)
	at com.arcadedb.server.http.handler.PostCommandHandler.executeScript(PostCommandHandler.java:118)
	at com.arcadedb.server.http.handler.PostCommandHandler.execute(PostCommandHandler.java:87)
	at com.arcadedb.server.http.handler.DatabaseAbstractHandler.execute(DatabaseAbstractHandler.java:91)
	at com.arcadedb.server.http.handler.AbstractHandler.handleRequest(AbstractHandler.java:128)
	at io.undertow.server.Connectors.executeRootHandler(Connectors.java:393)
	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:829)
Caused by: com.arcadedb.query.sql.parser.ParseException: Encountered " <SELECT> "SELECT "" at line 1, column 1.
Was expecting one of:
    <WHILE> ...
    <IF> ...
    <FOREACH> ...
    ";" ...
    
	at com.arcadedb.query.sql.parser.SqlParser.generateParseException(SqlParser.java:26917)
	at com.arcadedb.query.sql.parser.SqlParser.jj_consume_token(SqlParser.java:26762)
	at com.arcadedb.query.sql.parser.SqlParser.ParseScript(SqlParser.java:467)
	at com.arcadedb.query.sql.SQLQueryEngine.parseScript(SQLQueryEngine.java:218)
	... 14 more

Steps to reproduce

CREATE DOCUMENT TYPE doc;
CREATE PROPERTY doc.name STRING;
INSERT INTO doc SET name = 'test';

SELECT (SELECT name FROM doc).name;

Note: The following variants work as expected:

SELECT coalesce((SELECT name FROM doc)).name;

SELECT $temp.name LET $temp = (SELECT name FROM doc);

[gramian]

@lvca I tried to look into this and started at the grammar, where I found in Line 1753 that a projection returns a list of ProjectionItems. While looking at engine components handling subqueries, it seems lists of LetItems are returned. Could this difference be the issue here?

[lvca]

Not sure, but with the LET you can achieve the same result, right?

[gramian]

Yes, indeed one can use a let instead. The better workaround is applying coalesce which is behaving like an identity here.

I was however wondering why I can use Let-members directly, but not subquery members (without wrapping a function, iecoalesce, around). Particularly since internally subqueries seem to be let.

[gramian]

Reevaluating the error and the grammar, I think my previous idea was wrong, and the problem could rather be that a <DOT> following a subquery parenthesis (<RPAREN>) is just not allowed by the grammar currently.

Maybe an optional modifier needs to be allowed in the grammar for subqueries.

[gramian]

@lvca A related issue I just identified is a using more general parethesis expressions, ie:

SELECT (3+7).asString();

which yields an error. I think this is related (see https://github.com/ArcadeData/arcadedb/blob/main/engine/src/main/grammar/SQLGrammar.jjt#L2156 ?).

[gramian]

Some notes:

• I am trying to fix this via the grammar
• I am assuming the problem is rooted with the ParenthesisExpression
• I tried adding a Modifier block to ParenthesisExpression (after RPAREN), but this would require many changes to the ParenthesisExpression class, so this is not helpful
• I tried changing the "return" value of ParenthesisExpression from MathExpression to BaseExpression (and associated class changes), which does not seem to work.
• ParenthesisExpression is only used in FirstLevelExpression, but adding a Modifier block did not work. I also do not understand why this block works: MathExpression vs BaseExpression
• However, I currently think that the best way would be to handle the result of a ParenthesisExpression instead of inside the ParenthesisExpression, I think this means changing FirstLevelExpression but I don't know how yet.

[lvca]

Finally fixed!