Non-colocated correlated subqueries are not supported
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
A known limitation, but would be good to document.
Citus supports arbitrarily complex correlated subqueries as longs as the correlation is on the distribution key. An example from the regression tests where the LATERAL JOIN (e.g., correlated) is on the `user_id` column, which is the distribution key: https://github.com/citusdata/citus/blob/94736ce78dea560b0b26a252cc5e058345cb5ebe/src/test/regress/sql/multi_subquery_complex_queries.sql#L1053-L1093
An example of non-colocated LATERAL JOIN is follows:
```SQL
CREATE TABLE users (id text PRIMARY KEY,
name text,
created_at timestamptz DEFAULT now(),
last_see timestamptz);
CREATE TABLE todos (id bigserial PRIMARY KEY,
title text,
is_completed boolean,
is_public boolean,
created_at timestamp DEFAULT now(),
user_id text);
SELECT create_distributed_table('users', 'id');
SELECT create_distributed_table('todos', 'id');
alter table "public"."todos"
add constraint "todos_user_id_fkey"
foreign key ("user_id")
references "public"."users"
("id") on update restrict on delete restrict;
SELECT coalesce(json_agg("root"), '[]') AS "root"
FROM
(SELECT row_to_json(
(SELECT "_4_e"
FROM
(SELECT "_0_root.base"."id" AS "id",
"_0_root.base"."title" AS "title",
"_3_root.or.rel1"."rel1" AS "rel1") AS "_4_e")) AS "root"
FROM
(SELECT *
FROM "public"."todos"
WHERE ('true') ) AS "_0_root.base"
LEFT OUTER JOIN LATERAL
(SELECT row_to_json(
(SELECT "_2_e"
FROM
(SELECT "_1_root.or.rel1.base"."id" AS "id",
"_1_root.or.rel1.base"."name" AS "name") AS "_2_e")) AS "rel1"
FROM
(SELECT *
FROM "public"."users"
WHERE (("_0_root.base"."user_id") = ("id")) ) AS "_1_root.or.rel1.base") AS "_3_root.or.rel1" ON ('true')) AS "_5_root";
DEBUG: skipping recursive planning for the subquery since it contains references to outer queries
ERROR: could not run distributed query with subquery outside the FROM, WHERE and HAVING clauses
```
The above query failed because of another limitation, but the following simplified query also fails:
```SQL
SELECT *
FROM todos
LEFT JOIN LATERAL
(SELECT *
FROM users
WHERE user_id = id) AS foo ON (TRUE);
...
DEBUG: skipping recursive planning for the subquery since it contains references to outer queries
ERROR: complex joins are only supported when all distributed tables are co-located and joined on their distribution columns
```
And, even if we convert `users` table to a reference table, we'd get the following error on the complex query above:
```SQL
ERROR: cannot push down this subquery
DETAIL: Reference tables are not allowed in FROM clause when the query has subqueries in WHERE clause and it references a column from another query
```
related to #1862 and #3817
Contributor guide
Assessment
This issue has not been assessed yet.