Query returning duplicate values

[TristenHarr]

A query like this:

query MyQuery {
  user(
    where: {_and: [{_or: [{fullName: {_ilike: "%drew%"}}, {email: {_ilike: "%drew%"}}, {userTags: {tag: {value: {_ilike: "%drew%"}}}}]}]}
    order_by: {fullName: Asc}
  ) {
    fullName
    id
    userTags {
      tag {
        value
      }
    }
  }
}

Is returning duplicated values due to the tags.

{
  "data": {
    "user": [
      {
        "fullName": "Drew 1",
        "id": "79f91d1e",
        "userTags": [
          {
            "tag": {
              "value": "tag1"
            }
          },
          {
            "tag": {
              "value": "tag2"
            }
          }
        ]
      },
      {
        "fullName": "Drew 1",
        "id": "79f91d1e",
        "userTags": [
          {
            "tag": {
              "value": "tag1"
            }
          },
          {
            "tag": {
              "value": "tag2"
            }
          }
        ]
      },
      {
        "fullName": "Drew 2",
        "id": "b4e9504f",
        "userTags": [
          {
            "tag": {
              "value": "tag1"
            }
          }
        ]
      },
      {
        "fullName": "Andrew 1",
        "id": "4967ad1f",
        "userTags": [
          {
            "tag": {
              "value": "tag1"
            }
          },
          {
            "tag": {
              "value": "tag2"
            }
          }
        ]
      },
      {
        "fullName": "Andrew 1",
        "id": "4967ad1f",
        "userTags": [
          {
            "tag": {
              "value": "tag1"
            }
          },
          {
            "tag": {
              "value": "tag2"
            }
          }
        ]
      }
    ]
  }
}

Notice the duplicates for Drew 1 and Andrew 1.

The SQL generated misses a DISTINCT statement.

See the generated SQL:

SELECT
  coalesce(json_agg(row_to_json("%11_universe")), '[]') AS "universe"
FROM
  (
    SELECT
      *
    FROM
      (
        SELECT
          coalesce(json_agg(row_to_json("%12_rows")), '[]') AS "rows"
        FROM
          (
            SELECT
              "%0_user"."full_name" AS "fullName",
              "%0_user"."id" AS "id",
              "%1_RELATIONSHIP_userTags"."userTags" AS "userTags"
            FROM
              "public"."user" AS "%0_user"
              LEFT OUTER JOIN LATERAL (
                SELECT
                  row_to_json("%1_RELATIONSHIP_userTags") AS "userTags"
                FROM
                  (
                    SELECT
                      *
                    FROM
                      (
                        SELECT
                          coalesce(json_agg(row_to_json("%9_rows")), '[]') AS "rows"
                        FROM
                          (
                            SELECT
                              "%5_RELATIONSHIP_tag"."tag" AS "tag"
                            FROM
                              "public"."user_tags" AS "%4_user_tags"
                              LEFT OUTER JOIN LATERAL (
                                SELECT
                                  row_to_json("%5_RELATIONSHIP_tag") AS "tag"
                                FROM
                                  (
                                    SELECT
                                      *
                                    FROM
                                      (
                                        SELECT
                                          coalesce(json_agg(row_to_json("%7_rows")), '[]') AS "rows"
                                        FROM
                                          (
                                            SELECT
                                              "%6_tag"."value" AS "value"
                                            FROM
                                              "public"."tag" AS "%6_tag"
                                            WHERE
                                              ("%4_user_tags"."tag_id" = "%6_tag"."id")
                                          ) AS "%7_rows"
                                      ) AS "%7_rows"
                                  ) AS "%5_RELATIONSHIP_tag"
                              ) AS "%5_RELATIONSHIP_tag" ON ('true')
                            WHERE
                              ("%0_user"."id" = "%4_user_tags"."user_id")
                          ) AS "%9_rows"
                      ) AS "%9_rows"
                  ) AS "%1_RELATIONSHIP_userTags"
              ) AS "%1_RELATIONSHIP_userTags" ON ('true')
              INNER JOIN LATERAL (
                SELECT
                  *
                FROM
                  "public"."user_tags" AS "%2_BOOLEXP_user_tags"
                WHERE
                  (
                    "%0_user"."id" = "%2_BOOLEXP_user_tags"."user_id"
                  )
              ) AS "%2_BOOLEXP_user_tags" ON ('true')
              INNER JOIN LATERAL (
                SELECT
                  *
                FROM
                  "public"."tag" AS "%3_BOOLEXP_tag"
                WHERE
                  (
                    "%2_BOOLEXP_user_tags"."tag_id" = "%3_BOOLEXP_tag"."id"
                  )
              ) AS "%3_BOOLEXP_tag" ON ('true')
            WHERE
              (
                (
                  ("%0_user"."full_name" ~~* cast('%drew%' as "text"))
                  OR ("%0_user"."email" ~~* cast('%drew%' as "text"))
                )
                OR ("%3_BOOLEXP_tag"."value" ~~* cast('%drew%' as "text"))
              )
            ORDER BY
              "%0_user"."full_name" DESC
          ) AS "%12_rows"
      ) AS "%12_rows"
  ) AS "%11_universe"

[soupi]

Thanks for reporting. We are working on a fix.

[arjunyel]

@soupi thank you for taking a look! Any way you can cut a new release with the fix?

[soupi]

@arjunyel Yes, we've just deployed the fix to production and it should now be available for ddn cloud users.

[arjunyel]

@soupi sorry how do I use this in DDN cloud? The only control I have over versions only let's me do 0.6.0

[soupi]

@arjunyel no further action should be needed, it should just work as expected now. Please let me know if it doesn't.

[arjunyel]

@soupi its working great, thank so much for the quick and comprehensive fix!

[soupi]

Great to hear. Thanks for the update!