distsql: EXPLAIN(distsql) does not show subqueries

[justinj]

It would be helpful for debugging if EXPLAIN (DISTSQL) showed the plans for subqueries. As of today, it doesn't:

root@127.0.0.1:64155/defaultdb> explain (distsql) select (select * from x);
  automatic |                                                                                                                                                                url
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    false   | https://cockroachdb.github.io/distsqlplan/decode.html#eJyMjzFLBDEUhHt_xTLVnQTMtSmVEw7Uk1VsJMWSDOfimix5CRwc-99lk0IsBMuZee978y4I0fNp-KLAvGMHqzCn6CgS02q1gYM_w2iFMcwlr7ZVcDER5oI85okweBumQrnRUPDMwzhVou5uu43u3EcJn7KFQs_gmUy3edk_7O9er-_74-N5C7soxJJ_8JKHE2H0ov5foafMMQh_VfibbBXoT2xvSizJ8TlFV880eax71fCU3NJdE4fQosUuV98BAAD__1e-aic=
(1 row)

Jira issue: CRDB-4724

Epic CRDB-55590

[awoods187]

This also does not work for explain or explain analyze.

root@127.0.0.1:26257/art> explain select (select * from kv);
ERROR: subquery must return only one column, found 7
SQLSTATE: 42601
root@127.0.0.1:26257/art> explain analyze select (select * from kv);
ERROR: subquery must return only one column, found 7
SQLSTATE: 42601

[jordanlewis]

I think your example was using a table with more than one column - if you use a table with 1 column, it works okay:

demo@127.0.0.1:26257/defaultdb> explain select * from (select 1);;
           info
---------------------------
  distribution: local
  vectorized: true

  • values
    size: 1 column, 1 row

[jordanlewis]

Here's a better example of what a subquery looks like in ordinary explain. In EXPLAIN(distsql), it's still just an opaque VALUES node. I think this will probably get fixed naturally as we make the new execbuilder, but we can leave it open for tracking.

demo@127.0.0.1:26257/defaultdb> explain select (select a from a limit 1);;
                          info
--------------------------------------------------------
  distribution: local
  vectorized: true

  • root
  │
  ├── • values
  │     size: 1 column, 1 row
  │
  └── • subquery
      │ id: @S1
      │ original sql: (SELECT a FROM a LIMIT 1)
      │ exec mode: one row
      │
      └── • scan
            estimated row count: 1 (100% of the table)
            table: a@primary
            spans: LIMITED SCAN
            limit: 1
(18 rows)