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:
- 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}
- 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