error with function calls in the outer part of a lateral join
- 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
Assessment
This issue has not been assessed yet.