apache / apache/age

`coalesce()` with a boolean expression and a constant literal fails: COALESCE types boolean and agtype cannot be matched

Open
#2,511 1 comment 0 reactions 0 assignees View on GitHub
bug
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.