SpatialJoin not activated for ST_Distance(p, q) BETWEEN d1 and d2

[jagill]

The conditions

ST_DISTANCE(p1.p, p2.p) < 4 AND ST_DISTANCE(p1.p, p2.p) > 2

and

ST_DISTANCE(p1.p, p2.p) between 2 and 4

are the same, but the first triggers a Spatial Join and the second does not. Example queries:

EXPLAIN (TYPE DISTRIBUTED)
WITH points AS (
  SELECT
    ST_POINT(x, y) AS p
  FROM (
    VALUES
      (1, 1),
      (2, 2)
  ) AS t (x, y)
)
SELECT
  *
FROM points p1,
  points p2
WHERE
  ST_DISTANCE(p1.p, p2.p) < 4
  AND ST_DISTANCE(p1.p, p2.p) > 2

EXPLAIN (TYPE DISTRIBUTED)
WITH points AS (
  SELECT
    ST_POINT(x, y) AS p
  FROM (
    VALUES
      (1, 1),
      (2, 2)
  ) AS t (x, y)
)
SELECT
  *
FROM points p1,
  points p2
WHERE
  ST_DISTANCE(p1.p, p2.p) BETWEEN 2 AND 4

[mbasmanova]

Oh... This is because spatial join is triggered only for ST_DISTANCE(a, b) < r and ST_DISTANCE(a, b) <= r. In the first query, spatial join is trigged by < 4 with conjunct > 2 applied after the join. In the second query, there is no eligible condition. It would be cool to enhance the optimizer to trigger spatial joins ST_DISTANCE(a, b) between r1 and r2.

CC: @zhenxiao