Bug #113167 After creating the spatial index, ST_Disjoint gives the incorrect answer
Description:
After creating the spatial index, the ST_Disjoint statement gives the incorrect answer, although the statement is the same as the before one.
How to repeat:
Consider the following statements:
```sql
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:
```sql
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}
```
2. 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.
Version:
8.2.0
the latest version in Github:
87307d4ddd88405117e3f1e51323836d57ab1f57
Suggested fix:
The query results should be the same whether there is a spatial index or not.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: After creating the spatial index, the ST_Disjoint statement gives the incorrect answer, although the statement is the same as the before one. How to repeat: Consider the following statements: ```sql 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: ```sql 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} ```  2. 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. Version: 8.2.0 the latest version in Github: 87307d4ddd88405117e3f1e51323836d57ab1f57 Suggested fix: The query results should be the same whether there is a spatial index or not.