tae898 opened a new issue, #2587:
URL: https://github.com/apache/age/issues/2587
**Describe the bug**
A btree index scan on an `agtype` key that starts at a `>=` bound skips the
keys equal to the bound. `MATCH (n:P) WHERE n.id >= 995 RETURN n.id` through
the documented property expression index returns 996 to 1000; without the index
it returns 995 to 1000. A closed range `n.id >= 500 AND n.id <= 510` returns 10
rows instead of 11, and `a >= 5 AND a <= 5` returns none while `a = 5` returns
one.
It is not specific to Cypher or to expression indexes: a plain `agtype`
column with a default btree index does the same, for integer, float and string
values, and on a 10-row index that fits on one leaf page. Through the same
index, `>`, `<`, `<=` and `=` return the right rows, and so does `>=` when the
scan does not start at it (a `DESC` index, or `ORDER BY a DESC`). A `>=` bound
with no equal key (`a >= 4.5`) is also right. A `float8` column with the same
data and queries on the same server is right.
What I checked: `agtype_ops_btree` maps strategies 1 to 5 to `<`, `<=`, `=`,
`>`, `>=` on `(agtype, agtype)`; `agtype_btree_cmp` returns 0 for a stored
value against the equal constant, in both argument orders; and `agtype_ge`
returns true for that pair. So the functions agree that the values are equal,
and the row is lost when a forward scan is positioned at the `>=` bound. I have
not found the line responsible.
We found this in a benchmark: after the write phase, a read-back `MATCH
(q:Person) WHERE q.id >= $f RETURN q.id, ...` on AGE returned one person fewer
than eight other graph engines. The planner chose the index scan itself there
(2,000 persons), so no planner settings are involved in the application.
**How are you accessing AGE (Command line, driver, etc.)?**
psql, and psycopg 3 in the application (same results).
**What data setup do we need to do?**
```pgsql
CREATE EXTENSION IF NOT EXISTS age;
LOAD 'age';
SET search_path = ag_catalog, "$user", public;
SELECT create_graph('rng');
SELECT * FROM cypher('rng', $$ UNWIND range(1, 1000) AS i CREATE (:P {id:
i}) $$) AS (v agtype);
CREATE INDEX p_id ON rng."P" USING btree (agtype_access_operator(properties,
'"id"'::agtype));
ANALYZE rng."P";
```
**What is the necessary configuration info needed?**
None. On a table this small the planner may prefer a sequential scan, so the
repro sets `enable_seqscan = off` and `enable_bitmapscan = off` to make it take
the index.
**What is the command that caused the error?**
```pgsql
SET enable_seqscan = off;
SET enable_bitmapscan = off;
SELECT string_agg(v::text, ',') FROM cypher('rng', $$ MATCH (n:P) WHERE n.id
>= 995 RETURN n.id $$) AS (v agtype);
-- 996,997,998,999,1000
SELECT count(*) FROM cypher('rng', $$ MATCH (n:P) WHERE n.id >= 500 AND n.id
<= 510 RETURN n $$) AS (v agtype);
-- 10
```
The same without a graph:
```pgsql
CREATE TABLE t (a agtype);
INSERT INTO t SELECT i::text::agtype FROM generate_series(1, 10) i;
CREATE INDEX ON t (a);
SET enable_seqscan = off;
SET enable_bitmapscan = off;
SELECT string_agg(a::text, ',') FROM t WHERE a >= '5'::agtype; --
6,7,8,9,10
SELECT count(*) FROM t WHERE a >= '5'::agtype AND a <= '5'::agtype; -- 0
SELECT count(*) FROM t WHERE a = '5'::agtype; -- 1
SELECT string_agg(a::text, ',') FROM t WHERE a > '4'::agtype; --
5,6,7,8,9,10
```
**Expected behavior**
The index returns the rows the predicate selects without it: 995 to 1000; 11
rows; 5 to 10; 1; 1; 5 to 10.
**Environment (please complete the following information):**
The same results on every image tried:
| image | PostgreSQL | AGE |
|---|---|---|
| `apache/age:release_PG18_1.8.0` | 18.6 | 1.8.0 |
| `apache/age:dev_snapshot_master` | 18.6 | master |
| `apache/age:dev_snapshot_PG18` | 18.4 | PG18 branch |
| `apache/age:release_PG17_1.7.0` | 17.11 | 1.7.0 |
| `apache/age:release_PG16_1.6.0` | 16.10 | 1.6.0 |
| `pgvector/pgvector` pg18 + PGDG `postgresql-18-age` | 18.6 | 1.8.0 |
**Additional context**
Results through the index on the 1,000-vertex graph above, and on a
1,000-row plain `agtype` column:
| predicate | through the index | without the index |
|---|---|---|
| `>= 995` | 996 to 1000 | 995 to 1000 |
| `> 994` | 995 to 1000 | 995 to 1000 |
| `<= 5` | 1 to 5 | 1 to 5 |
| `>= 500 AND <= 510` | 10 rows | 11 rows |
| `= 995` | 1 row | 1 row |
| `>= 995`, `DESC` index | 995 to 1000 | 995 to 1000 |
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
