sql: multi-use common table expressions

[jordanlewis]

Split from #7029 which was closed when we merged support for single-use common table expressions in #20359.

Support common table expressions whose clauses are used more than once in the statement. This may require temporary tables or other novel infrastructure, since each referenced statement should be run at most once in the case of DML statements, and in the case of read-only statements, should probably be run at most once anyway for efficiency.

For example,

WITH x AS (SELECT ... FROM table ...),
          y AS (SELECT ... FROM x ...)
SELECT ... FROM table2 JOIN x ON ...

In this statement, the x clause is referenced more than once, which is not currently supported.

[petermattis]

Support common table expressions whose clauses are used more than once in the statement. This may require temporary tables or other novel infrastructure, since each referenced statement should be run at most once in the case of DML statements, and in the case of read-only statements, should probably be run at most once anyway for efficiency.

Note that the decision on whether to run a read-only statement once (and cache the results) or more than once is cost-based. Also, if a read-only statement is used more than once, it may be useful to "unnest" the statement in order to push-down filters or merge projections. I don't have examples on hand, but this is discussed in academic literature regarding optimizing CTEs.

[stevenross]

Would I be correct in saying this would allow the introspection query used in Postgraphile to run on CockroachDB?

The expressiveness of GraphQL for API development has seen the Postgraphile project gain considerable traction. Being able to use it with a database such as CockroachDB for its cloud native features would be very compelling.

I understand that this alone may not allow Postgraphile to run. Maybe a seperate issue could be created and tracked within #25468?

[BramGruneir]

@stevenross I added PostGraphile to #25470.

[Bessonov]

I'm trying to get the total count of matched rows, but return only a subset of them for pagination, like described in: https://stackoverflow.com/a/28888696/926620 :

WITH cte AS (
   SELECT *
   FROM   tbl
   WHERE  /* whatever */
   )
SELECT *
FROM  (
   TABLE  cte
   ORDER  BY col1
   LIMIT  ?
   OFFSET ?
   ) sub
RIGHT  JOIN (SELECT count(*) FROM cte) c(full_count) ON true;

but I get:

pq: unimplemented: unsupported multiple use of CTE clause "cte"
HINT: See: https://github.com/cockroachdb/cockroach/issues/21084

Is there any way to efficiently do that in CockroachDB?

BTW AFAIK OFFSET is very inefficient in Postgres. It's true for CockroachDB too? Colud it be avoided if instead of OFFSET something like WHERE id > ${lastId from previos page} ORDER BY id ASC LIMIT 10 is used?

[awoods187]

@Bessonov what version of CRDB are you using? In 19.2 (we have just released a release candidate https://www.cockroachlabs.com/docs/releases/#testing-releases) this should be supported but it isn't in early versions. For example:

WITH r AS (SELECT * FROM rides WHERE revenue > 98),
    results AS (SELECT * FROM users AS u, r WHERE r.rider_id = u.id)
  SELECT * FROM results;
                   id                  |     city      |       name       |            address             | credit_card |                  id                  |     city      | vehicle_city  |               rider_id               |              vehicle_id              |           start_address           |        end_address        |        start_time         |         end_time          | revenue
+--------------------------------------+---------------+------------------+--------------------------------+-------------+--------------------------------------+---------------+---------------+--------------------------------------+--------------------------------------+-----------------------------------+---------------------------+---------------------------+---------------------------+---------+
  ae147ae1-47ae-4800-8000-000000000022 | amsterdam     | Tyler Dalton     | 88194 Angela Gardens Suite 94  | 4443538758  | bbe76c8b-4395-4000-8000-00000000016f | amsterdam     | amsterdam     | ae147ae1-47ae-4800-8000-000000000022 | aaaaaaaa-aaaa-4800-8000-00000000000a | 45295 Brewer View Suite 52        | 62188 Jade Causeway       | 2018-12-17 03:04:05+00:00 | 2018-12-17 13:04:05+00:00 |   99.00

We have some documentation in our 19.2 version of the docs https://www.cockroachlabs.com/docs/v19.2/common-table-expressions.html#overview

[Bessonov]

@awoods187 thank you for your response! I was not able to build cockroachdb from sources. But now tested with 19.2 image and it does work as you stated!

Do you have any thoughts on OFFSET in last part of my question?

[awoods187]

We haven't yet written up docs but good information can be found here cockroachdb/docs#3743