`coalesce()` with a boolean expression and a constant literal fails: COALESCE types boolean and agtype cannot be matched
- Dominant language
- C
- Stars
- 4.8k
- Forks
- 523
- Avg merge
- 1d 2h
- Merged PRs (30d)
- 9
Description
**Bug description**
Writing a Cypher query that passes a boolean expression together with a constant literal to `coalesce()` — e.g. `coalesce(1 = 1, false)` — makes AGE generate a PostgreSQL COALESCE whose argument types do not match: the boolean comparison is compiled to a native `boolean`, while the constant branch remains `agtype`. PostgreSQL rejects the whole query at parse time with error 42804 `COALESCE types boolean and agtype cannot be matched`. The statement is valid Cypher (it returns a boolean in Neo4j) and is rejected before producing any result.
**Access method**
- Command line via `psql`, inside the official Docker container `apache/age:1.8.0`
**Data setup**
No data is required — the error reproduces on an empty graph. Only the graph itself must exist:
```pgsql
CREATE EXTENSION IF NOT EXISTS age;
LOAD 'age';
SET search_path = ag_catalog, "$user", public;
SELECT create_graph('graph_test');
```
**Configuration**
- None beyond the stock AGE extension. No additional modules (no PostGIS, etc.), default `search_path` handling as shown above.
**Command that triggers the error**
```pgsql
SELECT * FROM cypher('graph_test', $$ RETURN coalesce(1 = 1, false) AS c $$) AS (a agtype);
```
```
ERROR: COALESCE types boolean and agtype cannot be matched
LINE 1: ...OM cypher('graph_test', $$ RETURN coalesce(1 = 1, false) AS ...
^
```
The same error also occurs:
- when the boolean branch is a node-property comparison, e.g. `MATCH (n) RETURN coalesce(n.id = 42, false)`;
- when the constant branch is `true` or `null` instead of `false`;
- with aggregates, e.g. `RETURN coalesce(avg(x), 0)` (message: `COALESCE types double precision and agtype cannot be matched`).
Only coalesce calls whose arguments are all `agtype` already work, e.g. `RETURN coalesce(42, 0)` or `MATCH (n) RETURN coalesce(n.id, 0)`.
**Expected behavior**
The statement is valid Cypher: both arguments of `coalesce()` are booleans (or coercible to a common type), so it should run successfully and return `true` (the first non-null argument). A query mixing a comparison with a constant is routine and must not be rejected with a PostgreSQL type-coercion error. The bug is in AGE's SQL generation: the constant branch should be coerced to `agtype` so the generated COALESCE type-checks.
**Environment**
- Version: 1.8.0 (official `apache/age:1.8.0` Docker image)
- PostgreSQL: 18.1 (Debian 18.1-1.pgdg13+2), x86_64
Contributor guide
Research direction
Start by reproducing the cypher('graph_test', ...) query in the official apache/age:1.8.0 Docker image with psql, then trace AGE's SQL generation for coalesce() and its handling of constant branches. Done means the boolean example returns true without a PostgreSQL type error, while the property-comparison and aggregate cases also avoid the reported boolean/double-precision versus agtype mismatch.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- c, postgresql, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Clearly specified
- Newbie friendliness
- 55/100