citusdata / citusdata/citus

Unsupported clause type for COALESCE between to tables

Open
#1,732 4 comments 0 reactions 0 assignees View on GitHub
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

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.