jiaqizho opened a new issue, #1286:
URL: https://github.com/apache/cloudberry/issues/1286
### 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).
--
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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
