SQL: "EXPLAIN" does not resolve recursively

[gramian]

ArcadeDB Version:

ArcadeDB Server v24.1.1 (build 839d61f47c0eb80f5e56b0103c2686c85a572699/1709219005894/main)

OS and JDK Version:

Running on Mac OS X 12.7 - OpenJDK 64-Bit Server VM 17.0.9 (Homebrew)

Expected behavior

When using EXPLAIN with (at least some) subqueries, the subquery is just returned in the execution plan and not explained.

+ CHECK USERTYPE HIERARCHY (E) + LET (once) $__ARCADEDB_CREATE_EDGE_fromV = $a
+ LET (once) $__ARCADEDB_CREATE_EDGE_toV =
(+ FETCH FROM TYPE vec
 + FETCH FROM BUCKET 25 (vec_0) ASC = 0 RECORDS
 + FETCH FROM BUCKET 28 (vec_1) ASC = 0 RECORDS
 + FETCH FROM BUCKET 31 (vec_2) ASC = 0 RECORDS
 + FETCH FROM BUCKET 34 (vec_3) ASC = 0 RECORDS
 + FETCH FROM BUCKET 37 (vec_4) ASC = 0 RECORDS
 + FETCH FROM BUCKET 40 (vec_5) ASC = 0 RECORDS
 + FETCH FROM BUCKET 43 (vec_6) ASC = 0 RECORDS
 + FETCH FROM BUCKET 46 (vec_7) ASC = 0 RECORDS
 + FILTER ITEMS WHERE x = 1)
+ FOR EACH x in $__ARCADEDB_CREATE_EDGE_fromV FOR EACH y in $__ARCADEDB_CREATE_EDGE_toV CREATE EDGE vec FROM x TO y BIDIRECTIONAL
+ SAVE RECORD

This is a little hard to parse but I took the actual behavior and placed inside the subquery parentheses the result of the subquery explanation EXPLAIN SELECT FROM vec WHERE x = 1.

Actual behavior

+ CHECK USERTYPE HIERARCHY (E)
+ LET (once) $__ARCADEDB_CREATE_EDGE_fromV = $a
+ LET (once) $__ARCADEDB_CREATE_EDGE_toV = (SELECT FROM vec WHERE x = 1)
+ FOR EACH x in $__ARCADEDB_CREATE_EDGE_fromV FOR EACH y in $__ARCADEDB_CREATE_EDGE_toV CREATE EDGE vec FROM x TO y BIDIRECTIONAL
+ SAVE RECORD

Steps to reproduce

EXPLAIN CREATE EDGE vec FROM $a TO (SELECT FROM vec WHERE x = 1)

[lvca]

Fixed this issue by changing the API of SQL blocks: the profiling is not a parameter anymore, but it's saved in the execution context. This also allows us to save precious bytes (the profiling boolean was stored in each step of the query and assigned at creation).

This is the new output of the following command create edge Part_Of from (select from V limit 1) to (select from V limit 1 skip 1):

+ CHECK USERTYPE HIERARCHY (E) (18μs)
+ LET (once) (510μs)
|  $__ARCADEDB_CREATE_EDGE_fromV = (SELECT FROM V LIMIT 1)
    + FETCH FROM TYPE V (29μs)
      + FETCH FROM BUCKET 1 (V_0) ASC = 1 RECORDS (29μs)
      + FETCH FROM BUCKET 4 (V_1) ASC = 0 RECORDS ()
      + FETCH FROM BUCKET 7 (V_2) ASC = 0 RECORDS ()
      + FETCH FROM BUCKET 10 (V_3) ASC = 0 RECORDS ()
      + FETCH FROM BUCKET 13 (V_4) ASC = 0 RECORDS ()
      + FETCH FROM BUCKET 16 (V_5) ASC = 0 RECORDS ()
      + FETCH FROM BUCKET 19 (V_6) ASC = 0 RECORDS ()
      + FETCH FROM BUCKET 22 (V_7) ASC = 0 RECORDS ()
    + LIMIT ( LIMIT 1)
+ LET (once) (204μs)
|  $__ARCADEDB_CREATE_EDGE_toV = (SELECT FROM V SKIP 1 LIMIT 1)
    + FETCH FROM TYPE V (42μs)
      + FETCH FROM BUCKET 1 (V_0) ASC = 1 RECORDS (24μs)
      + FETCH FROM BUCKET 4 (V_1) ASC = 1 RECORDS (18μs)
      + FETCH FROM BUCKET 7 (V_2) ASC = 0 RECORDS ()
      + FETCH FROM BUCKET 10 (V_3) ASC = 0 RECORDS ()
      + FETCH FROM BUCKET 13 (V_4) ASC = 0 RECORDS ()
      + FETCH FROM BUCKET 16 (V_5) ASC = 0 RECORDS ()
      + FETCH FROM BUCKET 19 (V_6) ASC = 0 RECORDS ()
      + FETCH FROM BUCKET 22 (V_7) ASC = 0 RECORDS ()
    + SKIP ( SKIP 1)
    + LIMIT ( LIMIT 1)
+ FOR EACH x in $__ARCADEDB_CREATE_EDGE_fromV
    FOR EACH y in $__ARCADEDB_CREATE_EDGE_toV
       CREATE EDGE Part_Of FROM x TO y BIDIRECTIONAL (486μs)
+ SAVE RECORD

[gramian]

Running any of the examples above I get the error:

Error on command execution (PostCommandHandler)

``` java.lang.NullPointerException: Cannot invoke "com.arcadedb.query.sql.executor.InternalExecutionPlan.prettyPrint(int, int)" because the return value of "com.arcadedb.query.sql.parser.ParenthesisExpression.getExecutionPlan()" is null at com.arcadedb.query.sql.parser.Expression.prettyPrint(Expression.java:489) at com.arcadedb.query.sql.executor.GlobalLetExpressionStep.prettyPrint(GlobalLetExpressionStep.java:74) at com.arcadedb.query.sql.executor.SelectExecutionPlan.prettyPrint(SelectExecutionPlan.java:52) at com.arcadedb.server.http.handler.PostCommandHandler.lambda$execute$0(PostCommandHandler.java:117) at java.base/java.util.Optional.ifPresent(Optional.java:178) at com.arcadedb.server.http.handler.PostCommandHandler.execute(PostCommandHandler.java:117) 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: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:840) ```

[lvca]

Fixed, thanks!

[gramian]

@lvca After your fix I get (in studio) the following output for the command above:

+ CHECK EDGE TYPE
+ LET (once)
|  $__ARCADEDB_CREATE_EDGE_fromV = (SELECT FROM V LIMIT 1)
+ LET (once)
|  $__ARCADEDB_CREATE_EDGE_toV = (SELECT FROM V SKIP 1 LIMIT 1)
+ FOR EACH x in $__ARCADEDB_CREATE_EDGE_fromV
    FOR EACH y in $__ARCADEDB_CREATE_EDGE_toV
       CREATE EDGE Part_Of FROM x TO y BIDIRECTIONAL
+ SAVE RECORD

So the FETCHes and the timings per instruction are missing?

[lvca]

Let me know if now it's fixed.

[gramian]

Tried with the recent fix but still no change in output. Sorry.

[lvca]

It's working on my PC. Can you double check you're using the latest?

[gramian]

Strange. I am running build 6774b78045480908a9dd21a3801326d7b796a3f7/1709854848325/main freshly built and testing the output in studio and console, neither does show the FETCH nor timings. I have no idea what I am doing wrong?

PS: Using sqlscript language instead of sql, it seems EXPLAIN and PROFILE are just ignored.

[gramian]

In current v24.5.1-SNAPSHOT (build 71472c4c05ae9278d0d5d71a919b31883e1ea767/1714066325671 I still get:

+ CHECK EDGE TYPE
+ LET (once)
  + $__ARCADEDB_CREATE_EDGE_fromV = (SELECT FROM V LIMIT 1)
+ LET (once)
  + $__ARCADEDB_CREATE_EDGE_toV = (SELECT FROM V SKIP 1 LIMIT 1)
+ FOR EACH x in $__ARCADEDB_CREATE_EDGE_fromV
    FOR EACH y in $__ARCADEDB_CREATE_EDGE_toV
       CREATE EDGE Part_Of FROM x TO y BIDIRECTIONAL
+ SAVE RECORD

in studio for sql and sqlscript. In console, I get the above result for language sql and no output for sqlscript.