citusdata / citusdata/citus

error with function calls in the outer part of a lateral join

Open
#4,975 1 comment 0 reactions 0 assignees View on GitHub
Dominant language
C
Stars
12.8k
Forks
794
Avg merge
2d 14h
Merged PRs (30d)
31

Description

I'm using the schema as described in this [getting started](http://docs.citusdata.com/en/v10.0/use_cases/multi_tenant.html) page on a single node citus instance as described [here](http://docs.citusdata.com/en/v10.0/installation/single_node_docker.html).
```sql
CREATE TABLE companies (
id bigserial PRIMARY KEY,
name text NOT NULL,
image_url text,
created_at timestamp without time zone NOT NULL,
updated_at timestamp without time zone NOT NULL
);

CREATE TABLE campaigns (
id bigserial, -- was: PRIMARY KEY
company_id bigint REFERENCES companies (id),
name text NOT NULL,
cost_model text NOT NULL,
state text NOT NULL,
monthly_budget bigint,
blacklisted_site_urls text[],
created_at timestamp without time zone NOT NULL,
updated_at timestamp without time zone NOT NULL,
PRIMARY KEY (company_id, id) -- added
);
SELECT create_distributed_table('companies', 'id');
SELECT create_distributed_table('campaigns', 'company_id');

create function search_companies(search text)
returns setof companies as $$
begin
return query
select * from companies where name ilike ('%' || search || '%');
end;
$$ language plpgsql stable;
```

The following query works as expected:
```sql
SELECT
*
FROM
(SELECT * FROM companies WHERE name ILIKE '%hello%') AS c
LEFT OUTER JOIN LATERAL (
SELECT * FROM campaigns cs WHERE cs.company_id = c.id
) AS cs ON TRUE
```

This one fails with an error:
```sql
SELECT
*
FROM
search_companies('hello') AS c
LEFT OUTER JOIN LATERAL (
SELECT * FROM campaigns cs WHERE cs.company_id = c.id
) AS cs ON TRUE
```
```bash
ERROR: cannot pushdown the subquery
DETAIL: Complex subqueries and CTEs cannot be in the outer part of the outer join
```

Making `search_companies` a distributed function didn't help:
```sql
SELECT create_distributed_function(
'search_companies(text)'
);
```

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.