Postgres near operator does not work when neither of max / min distance are specified

[breathingcyborg]

Describe the Bug

When using near operator on a geometry/point field, the expected behavior is it should sort by distance from the given point. But sometimes is sorts by binary value of geometry/point column, at other times it completely removes order by in the sql query.

More details are in steps to reproduce section.

Link to the code that reproduces this issue

https://github.com/breathingcyborg/payload-near-field-issue-repro/tree/main

Reproduction Steps

• clone repo and setup db
• visit localhost:3000/seed to seed the data
• run the test cases below in graphql playground at localhost:3000/api/graphql-playground

Case 1

Near query without minDistance, maxDistance and sort.

Expectation: Since we're querying points around 72 lng, the points at 72 lng should be before points at 10 lng.

Output: The generated sql doesn't use coordinates specified, and sorts by created_at.

Graphql Query

query {
  Locations(
    where: { coords: { near: [72.536629103, 22.990990991] } },
  ) {
    docs {
      id
      coords
      name
    }
  }
}

Output

{
  "data": {
    "Locations": {
      "docs": [
        {
          "id": 4,
          "coords": [
            10.546312387,
            10.009009009
          ],
          "name": "A"
        },
        {
          "id": 3,
          "coords": [
            10.546312387,
            10.009009009
          ],
          "name": "B"
        },
        {
          "id": 2,
          "coords": [
            72.536629103,
            22.990990991
          ],
          "name": "A"
        },
        {
          "id": 1,
          "coords": [
            72.536629103,
            22.990990991
          ],
          "name": "B"
        }
      ]
    }
  }
}

SQL

Query: select count(*) from "locations"
Query: select "id", "name", "updated_at", "created_at", ST_AsGeoJSON(coords)::jsonb as "coords" from "locations" order by "locations"."created_at" desc limit $1 -- params: [10]

Case 2

Near operator without minDistance, and maxDistance but with sort on geometry/point field.

Expectation: Since we're querying points around 72 lng, the points at 72 lng should be before points at 10 lng. For sort i'm not sure what to expect when we sort by binary value of geometry/point column.

Output; generated sql does not use coordinates specified, it does sort by geometry/point column, but its actually sorting by binary value of the geometry which is probably not what user intented.

Graphql query

query {
  Locations(
    where: { coords: { near: [72.536629103, 22.990990991] } },
    sort: "coords"
  ) {
    docs {
      id
      coords
      name
    }
  }
}

Output

{
  "data": {
    "Locations": {
      "docs": [
        {
          "id": 3,
          "coords": [
            10.546312387,
            10.009009009
          ],
          "name": "B"
        },
        {
          "id": 4,
          "coords": [
            10.546312387,
            10.009009009
          ],
          "name": "A"
        },
        {
          "id": 1,
          "coords": [
            72.536629103,
            22.990990991
          ],
          "name": "B"
        },
        {
          "id": 2,
          "coords": [
            72.536629103,
            22.990990991
          ],
          "name": "A"
        }
      ]
    }
  }
}

SQL

Query: select count(*) from "locations"
Query: select "id", "name", "updated_at", "created_at", ST_AsGeoJSON(coords)::jsonb as "coords" from "locations" order by "locations"."coords" asc limit $1 -- params: [10]

Case 3

Near query without minDistance and maxDistance specified, but sorting by two columns one geometry/point column other string column.

Expectation: Since we're querying points around 72 lng, the points at 72 lng should be before points at 10 lng (which is correct in this case by only because those rows had smaller value of id). And because of sort by name i expect for coodinates with same distance they should be sorted by name column, so name A should appear before name B.

Output:

Graphql Query

query {
  Locations(
    where: { coords: { near: [72.536629103, 22.990990991] } },
    sort: "coords,name"
  ) {
    docs {
      id
      coords
      name
    }
  }
}

Output

{
  "data": {
    "Locations": {
      "docs": [
        {
          "id": 1,
          "coords": [
            72.536629103,
            22.990990991
          ],
          "name": "B"
        },
        {
          "id": 2,
          "coords": [
            72.536629103,
            22.990990991
          ],
          "name": "A"
        },
        {
          "id": 3,
          "coords": [
            10.546312387,
            10.009009009
          ],
          "name": "B"
        },
        {
          "id": 4,
          "coords": [
            10.546312387,
            10.009009009
          ],
          "name": "A"
        }
      ]
    }
  }
}

SQL

Query: select count(*) from "locations"
Query: select "id", "name", "updated_at", "created_at", ST_AsGeoJSON(coords)::jsonb as "coords" from "locations" limit $1 -- params: [10]

Which area(s) are affected? (Select all that apply)

db-postgres

Environment Info

Binaries:
  Node: 20.11.0
  npm: 10.8.0
  Yarn: 1.22.22
  pnpm: 9.0.6
Relevant Packages:
  payload: 3.35.0
  next: 15.3.0
  @payloadcms/db-postgres: 3.35.0
  @payloadcms/email-nodemailer: 3.35.0
  @payloadcms/graphql: 3.35.0
  @payloadcms/next/utilities: 3.35.0
  @payloadcms/payload-cloud: 3.35.0
  @payloadcms/richtext-lexical: 3.35.0
  @payloadcms/translations: 3.35.0
  @payloadcms/ui/shared: 3.35.0
  react: 19.1.0
  react-dom: 19.1.0
Operating System:
  Platform: linux
  Arch: x64
  Version: #59~22.04.1-Ubuntu SMP PREEMPT_DYNAMIC Wed Mar 19 17:07:41 UTC 2
  Available memory (MB): 23348
  Available CPU cores: 12

Root cause & what may be fix.

Just my thoughts — feel free do something completely different.

I think the problem is near operator is overloaded, it serves two different usecases/operators depending on weather min/max distance are specified. (or atleast what we expect after reading the docs where minDistance and maxDistance are optional)

Usecase 1: near operator without specifying min/max distance

sort rows by their distance from the point specified (this dosen't work)

Usecase 2: near with specifying min/max distance

filter points that are within the given distance (this almost works)

I agree with @r1tsuu, its is weird how near operator in where part of query, affects sorting.

So maybe the better way is to create 2 different operators

One for querying points that are within min/max distance of a specified coodinate. This should only effect where clause not sort.

Other for sorting by distance from given coordinate, this should only affect order by not where.

This would require us to figure out a way to specify coordinates for sort, which is a bit weird but i think separating it into 2 different usecases would be a better way to go.

Or maybe we could take some different approach to separate it into these 2 usecases.