Graphql-engine: "postgres query error" when using subscriptions and "_st_d_within" expression to compare columns of type geography

[valstu]

I tested postgis extension and _st_d_within expressions and noticed following bug.

First of all I have a table called messages and one of the columns is called location (type geography). I created following query:

query GetMessages($point: geography!, $distance: Float!) {
  messages(where: {location: {_st_d_within: {distance: $distance, from: $point}}}) {
    id
    location
    message
    createdAt
  }
}

and used following variables:

{
  "point": {
    "type": "Point",
    "coordinates": [ 52.2, 40.2 ]
  },
  "distance": 20.0
}

Everything works as expected, I get messages within the radius defined in variables.

If I change the same query to subscription:

subscription GetMessages($point: geography!, $distance: Float!) {
  messages(where: {location: {_st_d_within: {distance: $distance, from: $point}}}) {
    id
    location
    message
    createdAt
  }
}

and use same variables I get following error on graphiql:

{
  "data": null,
  "errors": [
    {
      "errors": [
        {
          "extensions": {
            "path": "$",
            "code": "unexpected"
          },
          "message": "postgres query error"
        }
      ]
    }
  ]
}

I also checked out the postgres logs and found out following error message:

postgres_1        | 2019-10-25 17:47:04.581 UTC [4773] ERROR:  parse error - invalid geometry
postgres_1        | 2019-10-25 17:47:04.581 UTC [4773] HINT:  "{"" <-- parse error at position 2 within geometry
postgres_1        | 2019-10-25 17:47:04.581 UTC [4773] STATEMENT:
postgres_1        |             select
postgres_1        |               _subs.result_id, _fld_resp.root as result
postgres_1        |               from
postgres_1        |                 unnest(
postgres_1        |                   $1::uuid[], $2::json[]
postgres_1        |                 ) _subs (result_id, result_vars)
postgres_1        |               left outer join lateral
postgres_1        |                 (
postgres_1        |             SELECT  coalesce(json_agg("root" ), '[]' ) AS "root" FROM  (SELECT  row_to_json((SELECT  "_4_e"  FROM  (SELECT  "_0_root.base"."id" AS "id", (ST_AsGeoJSON("_0_root.base"."location", 15, 4 ))::json AS "location", "_0_root.base"."message" AS "message", "_0_root.base"."createdAt" AS "createdAt", "_3_root.ar.root.comments_aggregate"."comments_aggregate" AS "comments_aggregate"       ) AS "_4_e"      ) ) AS "root" FROM  (SELECT  *  FROM "public"."messages"  WHERE ((ST_DWithin("public"."messages"."location", (("_subs"."result_vars"#>>ARRAY['query', 'point']))::geography, (("_subs"."result_vars"#>>ARRAY['query', 'distance']))::real, (("_subs"."result_vars"#>>ARRAY['synthetic', '0']))::boolean )))     ) AS "_0_root.base" LEFT OUTER JOIN LATERAL (SELECT  json_build_object('aggregate', json_build_object('count', COUNT(*) ) ) AS "comments_aggregate" FROM  (SELECT  1 AS "root.ar.root.comments_aggregate__one" FROM  (SELECT  *  FROM "public"."comment"  WHERE (("_0_root.base"."id") = ("messageId"))     ) AS "_1_root.ar.root.comments_aggregate.base"      ) AS "_2_root.ar.root.comments_aggregate"      ) AS "_3_root.ar.root.comments_aggregate" ON ('true')      ) AS "_5_root"
postgres_1        |                 ) _fld_resp ON ('true')

Seems like when using the subscription some kind of parse error is happening.

I also started a discussion about this recently on Discord and another user confirmed the same issue.

[KZhidovinov]

Hi! Have you any plans to fix GeoJSON parsing in subscription arguments?

[lexi-lambda]

Yes, updating #3344 so it can be merged is on my list of things to do, and I should get to it soon. Apologies for the delay!

[KZhidovinov]

As workaround I used the argument in WKT and subscription to custom function:

      create function get_ways_in_extent(wkt_extent text)
      returns setof way as $$
        select *
        from way_shapes w
        where ST_Intersects(w.shape, wkt_extent::geography)

      $$ language sql stable;

[arys]

Hello, is there any news?

[kzka90]

+1 Anyone working on this fix?

[lexi-lambda]

PR #3344 fixes this, but it still needs a couple final touches before merging, which is why it has not been merged yet. I would very much like to make those final touches, but neither I nor anyone else on the team have been able to get to it yet.

It is on our backlog, so I assure we have not forgotten about it! But if anyone is interested in getting the fix in faster, by all means, feel free to pick up from where #3344 left off (and I can give pointers to anyone interested).