[Bug] The result of query with geometric operators(polygon type) always be wrong
- 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
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