Hasura SQL Query id: Integer not working bug

Question

I created the following SQL query on Hasura.

CREATE OR REPLACE FUNCTION public.search_nearby_user(userId integer, distancekms integer)
 RETURNS SETOF users_nearby
 LANGUAGE sql
 STABLE
AS $function$
    SELECT A.userid, A.location, 
    (SELECT json_agg(row_to_json(B)) FROM users_location B
        WHERE(
            ST_Distance(
                ST_Transform(B.location::Geometry, 3857),
                ST_Transform(A.location::Geometry, 3857)
            ) / 1000) < distancekms
        ) AS nearbyUsers
    FROM users_location A WHERE A.userid = userId
$function$;

However, instead of just showing the one user with the right userId, it shows all users. Here's a sample query.

query {
  search_nearby_users(
    args: {userid: 1, distancekms: 1}
  ){
    userid
    location
    nearbyusers
  }
}

Here's the output of that query.

{
  "data": {
    "search_nearby_users": [
      {
        "userid": 1,
        "location": {
          "type": "Point",
          "crs": {
            "type": "name",
            "properties": {
              "name": "urn:ogc:def:crs:EPSG::4326"
            }
          },
          "coordinates": [
            43.75049,
            11.03207
          ]
        },
        "nearbyusers": [
          {
            "userid": 1,
            "location": "0101000020E6100000CEFC6A0E10E045408D5DA27A6B102640"
          },
          {
            "userid": 3,
            "location": "0101000020E6100000CEFC6A0E10E045408D5DA27A6B102640"
          }
        ]
      },
      {
        "userid": 3,
        "location": {
          "type": "Point",
          "crs": {
            "type": "name",
            "properties": {
              "name": "urn:ogc:def:crs:EPSG::4326"
            }
          },
          "coordinates": [
            43.75049,
            11.03207
          ]
        },
        "nearbyusers": [
          {
            "userid": 1,
            "location": "0101000020E6100000CEFC6A0E10E045408D5DA27A6B102640"
          },
          {
            "userid": 3,
            "location": "0101000020E6100000CEFC6A0E10E045408D5DA27A6B102640"
          }
        ]
      }
    ]
  }
}

When I change userId to a number (i.e. 1) in the SQL, it works though. For example:

CREATE OR REPLACE FUNCTION public.search_nearby_user(userid integer, distancekms integer)
 RETURNS SETOF users_nearby
 LANGUAGE sql
 STABLE
AS $function$
    SELECT A.userid, A.location, 
    (SELECT json_agg(row_to_json(B)) FROM users_location B
        WHERE(
            ST_Distance(
                ST_Transform(B.location::Geometry, 3857),
                ST_Transform(A.location::Geometry, 3857)
            ) / 1000) < distancekms
        ) AS nearbyUsers
    FROM users_location A WHERE A.userid = 1
$function$

Now it shows only one user, indicating that the rest of SQL query is implemented correctly.

{
  "data": {
    "search_nearby_user": [
      {
        "userid": 1,
        "location": {
          "type": "Point",
          "crs": {
            "type": "name",
            "properties": {
              "name": "urn:ogc:def:crs:EPSG::4326"
            }
          },
          "coordinates": [
            43.75049,
            11.03207
          ]
        },
        "nearbyusers": [
          {
            "userid": 1,
            "location": "0101000020E6100000CEFC6A0E10E045408D5DA27A6B102640"
          },
          {
            "userid": 3,
            "location": "0101000020E6100000CEFC6A0E10E045408D5DA27A6B102640"
          }
        ]
      }
    ]
  }
}

I don't know what I'm doing wrong :( I only want the user for which I specified the userId.

I also added a Loom video to explain the bug via video.

https://www.loom.com/share/b8dda4d0728d435a910599c632b27f6e

Is the SQL Server tag a mistake and should be Postgres instead? — sticky bit, May 15 '20 at 01:53

score 3 · Answer 1 · answered May 15 '20 at 02:06

PostgreSQL convention for names is _. When using camelCase you need to add double quotes to the identifier. I believe that is your issue

CREATE OR REPLACE FUNCTION public.search_nearby_user(userId integer, distancekms integer)
 RETURNS SETOF users_nearby
 LANGUAGE sql
 STABLE
AS $function$
    SELECT A.userid, A.location, 
    (SELECT json_agg(row_to_json(B)) FROM users_location B
        WHERE(
            ST_Distance(
                ST_Transform(B.location::Geometry, 3857),
                ST_Transform(A.location::Geometry, 3857)
            ) / 1000) < distancekms
        ) AS nearbyUsers
    FROM users_location A WHERE A.userid = "userId"
$function$;

But Hasura supports ST_Distance in their GraphQL schema by default. There is no need to create a function for that. You could try:

query {
  user (where: {location: {_cast: {geometry: {_st_within: 10000}}}}}) {
    user_location
  }
}

Hasura SQL Query id: Integer not working bug

1 Answers1