Error in propagating commands to workers in repartition joins
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
The SELECT query below can be executed on a local table but not on a distributed table.
```sql
[local] v-naugur@postgres:9700-20485=# CREATE TABLE t1(c0 inet , c1 DECIMAL , c2 DECIMAL);
CREATE TABLE
Time: 23.011 ms
[local] v-naugur@postgres:9700-20485=# CREATE TABLE t3(c0 inet , c1 DECIMAL , c2 DECIMAL);
CREATE TABLE
Time: 20.387 ms
[local] v-naugur@postgres:9700-20485=# SELECT ALL SUM(0.5891452039287028430436521375668235123157501220703125), CAST(length((('-1e500')||(-804519262))) AS INT) FROM t1, ONLY t3 WHERE ((((t1.c2)=(t3.c1)))AND((('25.95.51.180')::VARCHAR COLLATE "en_US.utf8")~*to_ascii(CAST(t3.c2 AS VARCHAR(5))))) GROUP BY t3.c1 HAVING BOOL_AND((t3.c1) BETWEEN (t1.c1) AND (t3.c2));
┌─────┬────────┐
│ sum │ length │
├─────┼────────┤
└─────┴────────┘
(0 rows)
Time: 1.298 ms
[local] v-naugur@postgres:9700-20485=# SELECT create_distributed_table('t1', 'c2');
┌──────────────────────────┐
│ create_distributed_table │
├──────────────────────────┤
│ │
└──────────────────────────┘
(1 row)
Time: 52.998 ms
[local] v-naugur@postgres:9700-20485=# SELECT create_distributed_table('t3', 'c0');
┌──────────────────────────┐
│ create_distributed_table │
├──────────────────────────┤
│ │
└──────────────────────────┘
(1 row)
Time: 54.030 ms
[local] v-naugur@postgres:9700-20485=# SELECT ALL SUM(0.5891452039287028430436521375668235123157501220703125), CAST(length((('-1e500')||(-804519262))) AS INT) FROM t1, ONLY t3 WHERE ((((t1.c2)=(t3.c1)))AND((('25.95.51.180')::VARCHAR COLLATE "en_US.utf8")~*to_ascii(CAST(t3.c2 AS VARCHAR(5))))) GROUP BY t3.c1 HAVING BOOL_AND((t3.c1) BETWEEN (t1.c1) AND (t3.c2));
...
NOTICE: issuing SELECT sum(0.5891452039287028430436521375668235123157501220703125) AS sum, 16 AS length, "pg_merge_job_343681269771.task_000005".intermediate_column_343681269771_0 AS worker_column_3 FROM (pg_merge_job_343681269770.task_000005 "pg_merge_job_343681269770.task_000005" JOIN pg_merge_job_343681269771.task_000005 "pg_merge_job_343681269771.task_000005" ON (("pg_merge_job_343681269770.task_000005".intermediate_column_343681269770_0 OPERATOR(pg_catalog.=) "pg_merge_job_343681269771.task_000005".intermediate_column_343681269771_0))) WHERE true GROUP BY "pg_merge_job_343681269771.task_000005".intermediate_column_343681269771_0 HAVING bool_and((("pg_merge_job_343681269771.task_000005".intermediate_column_343681269771_0 OPERATOR(pg_catalog.>=) any_value("pg_merge_job_343681269770.task_000005".intermediate_column_343681269770_0)) AND ("pg_merge_job_343681269771.task_000005".intermediate_column_343681269771_0 OPERATOR(pg_catalog.<=) "pg_merge_job_343681269771.task_000005".intermediate_column_343681269771_0)))
DETAIL: on server v-naugur@localhost:9701 connectionId: 65
...
DETAIL: on server v-naugur@localhost:9702 connectionId: 99
ERROR: aggregate function calls cannot be nested
CONTEXT: while executing command on localhost:9701
Time: 508.514 ms
```
Here is the command running on a worker:
```sql
SELECT
sum(0.5891452039287028430436521375668235123157501220703125) AS sum,
16 AS length,
"pg_merge_job_343681269771.task_000010".intermediate_column_343681269771_0 AS worker_column_3
FROM (
pg_merge_job_343681269770.task_000010 "pg_merge_job_343681269770.task_000010"
JOIN pg_merge_job_343681269771.task_000010 "pg_merge_job_343681269771.task_000010"
ON (("pg_merge_job_343681269770.task_000010".intermediate_column_343681269770_0 OPERATOR(pg_catalog.=) "pg_merge_job_343681269771.task_000010".intermediate_column_343681269771_0))
)
WHERE true
GROUP BY "pg_merge_job_343681269771.task_000010".intermediate_column_343681269771_0
HAVING bool_and(
(
("pg_merge_job_343681269771.task_000010".intermediate_column_343681269771_0 OPERATOR(pg_catalog.>=) any_value("pg_merge_job_343681269770.task_000010".intermediate_column_343681269770_0))
AND ("pg_merge_job_343681269771.task_000010".intermediate_column_343681269771_0 OPERATOR(pg_catalog.<=) "pg_merge_job_343681269771.task_000010".intermediate_column_343681269771_0))
)
```
The issue is caused by the nested aggregate in the HAVING clause, with `bool_and` outside and `any_value` inside.
Contributor guide
Assessment
This issue has not been assessed yet.