citusdata / citusdata/citus

Non-colocated correlated subqueries are not supported

Open
#4,160 0 comments 0 reactions 0 assignees View on GitHub
application-development sql
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.