Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

After creating the spatial index, ST_Disjoint gives the incorrect answer #22

Open
cuteDen-ECNU opened this issue Nov 21, 2023 · 1 comment

Comments

@cuteDen-ECNU
Copy link
Owner

cuteDen-ECNU commented Nov 21, 2023

Consider the following statements:

DROP table if EXISTS t1;
create table t1(id INT, geom geometry not null srid 0);
insert into t1 (id, geom) values(0, st_geomfromtext('POLYGON((8 6,7 7,8 8,8 6))', 0));
insert into t1 (id, geom) values(1, st_geomfromtext('POLYGON((10 10,0 1,0 2,10 10))', 0));
SELECT  a1.id, a2.id FROM t1 As a1 JOIN t1 As a2 ON ST_Disjoint(a1.geom, a2.geom) WHERE a1.id <> a2.id;
-- result {1, 0; 0, 1}

create spatial index spidx on t1(geom);
SELECT a1.id, a2.id FROM t1 As a1 JOIN t1 As a2 ON ST_Disjoint(a1.geom, a2.geom) WHERE a1.id <> a2.id;
-- expected {1, 0; 0, 1}  
-- actual {null}

After creating the spatial index, the ST_Disjoint statement gives the incorrect answer, although the statement is the same as the before one.

The result of the second query should be {1, 0; 0, 1}, according to the following reasons:

  1. The two polygons do not intersect refer to the following statement and figure:
SELECT ST_Disjoint(st_geomfromtext('POLYGON((8 6,7 7,7 6,8 6))'), st_geomfromtext('POLYGON((10 10,0 1,0 2,10 10))', 0));
--result{1}

a

  1. Table a1 and a2 should be JOINed because ST_Disjoint(a1.geom, a2.geom) returns 1.

Returns 1 or 0 to indicate whether g1 is spatially disjoint from (does not intersect) g2.

Besides, this issue is similar with https://bugs.mysql.com/bug.php?id=107424

Version:
8.2.0
the latest version in Github:
87307d4ddd88405117e3f1e51323836d57ab1f57

@cuteDen-ECNU
Copy link
Owner Author

cuteDen-ECNU commented Nov 21, 2023

Bugtracker link: https://bugs.mysql.com/bug.php?id=113167

@cuteDen-ECNU cuteDen-ECNU changed the title After creating the spatial index, the ST_Disjoint gives the incorrect answer After creating the spatial index, ST_Disjoint gives the incorrect answer Nov 21, 2023
@cuteDen-ECNU cuteDen-ECNU added bug-confirm mysql GIS sytax trans Add point to the boundary of geometry index and removed sytax trans Add point to the boundary of geometry labels Nov 23, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant