Description:
ST_Disjoint:Returns 1 or 0 to indicate whether g1 is spatially disjoint from (does not intersect) g2.
At first, I created a spatial index on a column, it generated unexpected filtering on the WHERE condition.
Considering following query:
CREATE TABLE t1(geom geometry NOT NULL SRID 0);
CREATE TABLE t2(geom geometry);
INSERT INTO t1 (geom) VALUES(ST_GeomFromText('LINESTRING(2 0,1 1,2 2,2 0)'));
INSERT INTO t2 (geom) VALUES(ST_GeomFromText('POLYGON((4 4,0 1,0 2,4 4))'));
CREATE SPATIAL INDEX spidx1 ON t1(geom);
SELECT ST_Disjoint(t1.geom, t2.geom) FROM t1, t2 WHERE ST_Disjoint(t1.geom, t2.geom);
--expected: {1}
--actual : {null}
Then I tried following queries to directly observe its results:
SELECT ST_Disjoint(t1.geom, t2.geom) FROM t1, t2;
--result: {1}
SELECT ST_Disjoint(t1.geom, t2.geom) FROM t1, t2 WHERE ST_Disjoint(t1.geom, t2.geom) > 0;
--result: {1}
This is very confusing for me.The result of ST_Disjoint (t1. geom, t2. geom) is 1, which means TRUE, but it was filtered out during the WHERE condition filtering.
How to repeat:
Furthermore, in order to assist in identifying the cause of the error to the best of my ability, I have also tested various joins, hoping to be helpful to you!
Queries below are a detailed reproduce for this issue, please check.
CREATE TABLE t1(geom geometry NOT NULL SRID 0);
CREATE TABLE t2(geom geometry);
INSERT INTO t1 (geom) VALUES(ST_GeomFromText('LINESTRING(2 0,1 1,2 2,2 0)'));
INSERT INTO t2 (geom) VALUES(ST_GeomFromText('POLYGON((4 4,0 1,0 2,4 4))'));
CREATE SPATIAL INDEX spidx1 ON t1(geom);
SELECT ST_Disjoint(t1.geom, t2.geom) FROM t1, t2 WHERE ST_Disjoint(t1.geom, t2.geom);
-- expected:{1}
-- actual:{null}
SELECT ST_Disjoint(t1.geom, t2.geom) FROM t1, t2;
-- result: {1}
SELECT ST_Disjoint(t1.geom, t2.geom) FROM t1, t2 WHERE ST_Disjoint(t1.geom, t2.geom) > 0;
-- result: {1}
SELECT ST_Disjoint(t1.geom, t2.geom) FROM t1 INNER JOIN t2 ON ST_Disjoint(t1.geom, t2.geom);
-- expected:{1}
-- actual:{null}
SELECT ST_Disjoint(t1.geom, t2.geom) FROM t1 CROSS JOIN t2 ON ST_Disjoint(t1.geom, t2.geom);
-- expected:{1}
-- actual:{null}
SELECT ST_Disjoint(t1.geom, t2.geom) FROM t1 LEFT JOIN t2 ON ST_Disjoint(t1.geom, t2.geom);
-- result: {1} for left/right join, the result is correct
SELECT ST_Disjoint(t1.geom, t2.geom) FROM t1 LEFT JOIN t2 ON ST_Disjoint(t1.geom, t2.geom);
-- result: {1}
Description: ST_Disjoint:Returns 1 or 0 to indicate whether g1 is spatially disjoint from (does not intersect) g2. At first, I created a spatial index on a column, it generated unexpected filtering on the WHERE condition. Considering following query: CREATE TABLE t1(geom geometry NOT NULL SRID 0); CREATE TABLE t2(geom geometry); INSERT INTO t1 (geom) VALUES(ST_GeomFromText('LINESTRING(2 0,1 1,2 2,2 0)')); INSERT INTO t2 (geom) VALUES(ST_GeomFromText('POLYGON((4 4,0 1,0 2,4 4))')); CREATE SPATIAL INDEX spidx1 ON t1(geom); SELECT ST_Disjoint(t1.geom, t2.geom) FROM t1, t2 WHERE ST_Disjoint(t1.geom, t2.geom); --expected: {1} --actual : {null} Then I tried following queries to directly observe its results: SELECT ST_Disjoint(t1.geom, t2.geom) FROM t1, t2; --result: {1} SELECT ST_Disjoint(t1.geom, t2.geom) FROM t1, t2 WHERE ST_Disjoint(t1.geom, t2.geom) > 0; --result: {1} This is very confusing for me.The result of ST_Disjoint (t1. geom, t2. geom) is 1, which means TRUE, but it was filtered out during the WHERE condition filtering. How to repeat: Furthermore, in order to assist in identifying the cause of the error to the best of my ability, I have also tested various joins, hoping to be helpful to you! Queries below are a detailed reproduce for this issue, please check. CREATE TABLE t1(geom geometry NOT NULL SRID 0); CREATE TABLE t2(geom geometry); INSERT INTO t1 (geom) VALUES(ST_GeomFromText('LINESTRING(2 0,1 1,2 2,2 0)')); INSERT INTO t2 (geom) VALUES(ST_GeomFromText('POLYGON((4 4,0 1,0 2,4 4))')); CREATE SPATIAL INDEX spidx1 ON t1(geom); SELECT ST_Disjoint(t1.geom, t2.geom) FROM t1, t2 WHERE ST_Disjoint(t1.geom, t2.geom); -- expected:{1} -- actual:{null} SELECT ST_Disjoint(t1.geom, t2.geom) FROM t1, t2; -- result: {1} SELECT ST_Disjoint(t1.geom, t2.geom) FROM t1, t2 WHERE ST_Disjoint(t1.geom, t2.geom) > 0; -- result: {1} SELECT ST_Disjoint(t1.geom, t2.geom) FROM t1 INNER JOIN t2 ON ST_Disjoint(t1.geom, t2.geom); -- expected:{1} -- actual:{null} SELECT ST_Disjoint(t1.geom, t2.geom) FROM t1 CROSS JOIN t2 ON ST_Disjoint(t1.geom, t2.geom); -- expected:{1} -- actual:{null} SELECT ST_Disjoint(t1.geom, t2.geom) FROM t1 LEFT JOIN t2 ON ST_Disjoint(t1.geom, t2.geom); -- result: {1} for left/right join, the result is correct SELECT ST_Disjoint(t1.geom, t2.geom) FROM t1 LEFT JOIN t2 ON ST_Disjoint(t1.geom, t2.geom); -- result: {1}