apache / apache/cloudberry

[Bug] The result of query with geometric operators(polygon type) always be wrong

Open
#1,286 2 comments 2 reactions 0 assignees View on GitHub
type: Bug
Dominant language
C
Stars
1.4k
Forks
247
Avg merge
4d 3h
Merged PRs (30d)
39

Description

### Apache Cloudberry version

main

### What happened

sql - create `POINT_TBL`

```
CREATE TABLE POINT_TBL(f1 point);
INSERT INTO POINT_TBL(f1) VALUES ('(0.0,0.0)');
INSERT INTO POINT_TBL(f1) VALUES ('(-10.0,0.0)');
INSERT INTO POINT_TBL(f1) VALUES ('(-3.0,4.0)');
INSERT INTO POINT_TBL(f1) VALUES ('(5.1, 34.5)');
INSERT INTO POINT_TBL(f1) VALUES ('(-5.0,-12.0)');
INSERT INTO POINT_TBL(f1) VALUES ('(1e-300,-1e-300)');
INSERT INTO POINT_TBL(f1) VALUES ('(1e+300,Inf)');
INSERT INTO POINT_TBL(f1) VALUES ('(Inf,1e+300)');
INSERT INTO POINT_TBL(f1) VALUES (' ( Nan , NaN ) ');
INSERT INTO POINT_TBL(f1) VALUES ('10.0,10.0');
INSERT INTO POINT_TBL(f1) VALUES (NULL);
SELECT * FROM POINT_TBL;
```

and the query with polygon expression
```
SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)';
```

**the result of `SeqScan` and `IndexOnlyScan`/`IndexScan` is different.**
```
postgres=# set enable_indexscan to off;
SET
postgres=# set enable_indexonlyscan to off;
SET
postgres=# set enable_seqscan to on;
SET
postgres=# explain SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)';
QUERY PLAN
------------------------------------------------------------------------------------------
Aggregate (cost=1.07..1.08 rows=1 width=8)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..1.07 rows=1 width=0)
-> Seq Scan on point_tbl (cost=0.00..1.05 rows=1 width=0)
Filter: (f1 <@ '((0,0),(0,100),(100,100),(50,50),(100,0),(0,0))'::polygon)
Optimizer: Postgres query optimizer
(5 rows)

postgres=# SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)';
count
-------
5
(1 row)

postgres=#
postgres=# set enable_seqscan to off;
SET
postgres=# set enable_indexscan to on;
SET
postgres=# set enable_indexonlyscan to on;
SET
postgres=# explain SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)';
QUERY PLAN
----------------------------------------------------------------------------------------------
Aggregate (cost=8.17..8.18 rows=1 width=8)
-> Gather Motion 3:1 (slice1; segments: 3) (cost=0.13..8.17 rows=1 width=0)
-> Index Only Scan using gpointind on point_tbl (cost=0.13..8.15 rows=1 width=0)
Index Cond: (f1 <@ '((0,0),(0,100),(100,100),(50,50),(100,0),(0,0))'::polygon)
Optimizer: Postgres query optimizer
(5 rows)

postgres=# SELECT count(*) FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)';
count
-------
4
(1 row)
```

Also i found another problem: **The point `((1e-300,-1e-300))` always in the result.**
```
postgres=# set enable_indexscan to off;
SET
postgres=# set enable_indexonlyscan to off;
SET
postgres=# set enable_seqscan to on;
SET
postgres=# SELECT * FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)';
f1
------------------
(1e-300,-1e-300)
(NaN,NaN)
(0,0)
(5.1,34.5)
(10,10)
(5 rows)

postgres=# set enable_seqscan to off;
SET
postgres=# set enable_indexscan to on;
SET
postgres=# set enable_indexonlyscan to on;
SET
postgres=# SELECT * FROM point_tbl WHERE f1 <@ polygon '(0,0),(0,100),(100,100),(50,50),(100,0),(0,0)';
f1
------------------
(0,0)
(5.1,34.5)
(10,10)
(1e-300,-1e-300)
(4 rows)
```

i guess the polygon looks like(not sure):
```
y

| (0,100) *───────────────────────* (100,100)
| │ /
| │ /
| │ /
| │ /
| │ /
| │ /
| │ /
| │ (50,50) *
| │ \
| │ \
| │ \
| │ \
| │ \
| │ \
| │ \
| (0,0) *──┼───────────────────────* (100,0)
|
|
└───────────────────────────────────> x
```

And the point `(1e-300,-1e-300)` should be left of the line `((0,0) , (0,100))`, because its `Y(-1e-300)` is a neg value.

### What you think should happen instead

_No response_

### How to reproduce

nope

### Operating System

all

### Anything else

_No response_

### Are you willing to submit PR?

- [ ] Yes, I am willing to submit a PR!

### Code of Conduct

- [x] I agree to follow this project's [Code of Conduct](https://github.com/apache/cloudberry/blob/main/CODE_OF_CONDUCT.md).

Contributor guide

Open the contributing guide

Research direction

Start with the SQL reproduction in the issue and compare the results of the sequential scan with the index and index-only scans for the polygon containment operator. Verify the handling of the near-zero negative point and NaN values, then ensure both scan paths return the same count and exclude points outside the polygon. No source files or tests are named, so locating the geometric operator and index implementation is part of the work.

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
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.