Unsupported clause type for COALESCE between to tables
- Dominant language
- C
- Stars
- 12.8k
- Forks
- 794
- Avg merge
- 2d 14h
- Merged PRs (30d)
- 31
Description
Hi,
When a COALESCE is used in the WHERE clause while COALESCE is used for two columns, citus throws an error `ERROR: unsupported clause type.
_Tested on the version 6.2-4_
Setup
```sql
CREATE TABLE a (id text, duration int);
create table b (test_id text, sa_duration int);
SELECT create_distributed_table('a', 'id');
SELECT create_distributed_table('b', 'test_id');
INSERT INTO a (id) VALUES ('1');
INSERT INTO a (id) VALUES ('2');
INSERT INTO a VALUES ('3', 123123);
INSERT INTO b VALUES ('1', 1289312);
INSERT INTO b VALUES ('2', 123123);
INSERT INTO b VALUES ('3', 9999);
```
```
p=# select coalesce(a.duration,b.sa_duration) from a left join b on a.id = b.test_id where coalesce(a.duration,b.sa_duration) > 1;
ERROR: unsupported clause type
```
```
p=# select coalesce(a.duration,b.sa_duration) from a left join b on a.id = b.test_id where a.duration > 1;
-[ RECORD 1 ]----
coalesce | 123123
```
It works fine if the tables are not sharded via citus, or if both columns are from the same table.
Contributor guide
Assessment
This issue has not been assessed yet.