Filtering the same field with OR operator results in AND in generated SQL

[evolkmann]

I want to filter a text[] field by checking if it contains the variable $documentType. With a second variable I want to include or exclude records where the array field is null. To achieve this, I combine both conditions with or, but the resulting SQL contains an and, regardless if I use or or and in the GraphQL query.

I wonder if this is indeed a bug or if I use the filter options in the wrong way.

Versions / Settings

• PostgreSQL: 13.2
• pg: 8.6.0
• postgraphile: 4.12.3
• postgraphile-plugin-connection-filter: 2.2.1
• I have not set any graphileBuildOptions

Minimal Schema

create table demo (
    id serial primary key,
    only_for_document_types text[]
);

create index on demo using GIN(only_for_document_types);

insert into demo
  (only_for_document_types)
values
  (null),
  ('{example}');

Query

query Demo(
  $documentType: String
  $includeRecordsAvailableForAllDocTypes: Boolean = true
) {
  demo(
    filter: {
      or: {
        onlyForDocumentTypes: { isNull: $includeFieldsAvailableForAllDocTypes }
      }
      onlyForDocumentTypes: { anyEqualTo: $documentType }
    }
  ) {
    nodes {
      id
      onlyForDocumentTypes
    }
  }
}

Variables

{
    "documentType": "example"
}

Generated SQL

with __local_0__ as (
  select to_json(
    (
      json_build_object(
        '__identifiers'::text,
        json_build_array(__local_1__."id"),
        'id'::text,
        (__local_1__."id"),
        'onlyForDocumentTypes'::text,
        (
          case when (__local_1__."only_for_document_types") is null then null when coalesce(
            array_length(
              (__local_1__."only_for_document_types"),
              1
            ),
            0
          ) = 0 then '[]'::json else (
            select json_agg(__local_2__)
            from unnest((__local_1__."only_for_document_types")) as __local_2__
          ) end
        )
      )
    )
  ) as "@nodes"
  from (
    select __local_1__.*
    from "public"."demo" as __local_1__
    where (
      (
        (
          $1 = ANY (__local_1__."only_for_document_types")
        )
      )

      # ⚠️ PROBLEM IS HERE ⚠️
      # At this point an `and` combination is made, regardless of wether I specify `or` or `and`
      # in the GraphQL query. If it was `or` (like I specified in the GraphQL query, it would return
      # the expected results.
      and (
        (
          ((__local_1__."only_for_document_types" IS NULL))
        )
      )
    ) and (TRUE) and (TRUE)
    order by __local_1__."id" ASC
  ) __local_1__
),
__local_3__ as (
  select json_agg(
    to_json(__local_0__)
  ) as data
  from __local_0__
)
select coalesce(
  (
    select __local_3__.data
    from __local_3__
  ),
  '[]'::json
) as "data"

Simple, manually created query

The query below is the manual equivalent of what I would expect from my GraphQL query above and returns the expected results.

select *
from demo
where
    only_for_document_types is null
    or
    'example' = any(only_for_document_types)

Workaround

fragment Nodes on DemoConnection {
  nodes {
    id
    onlyForDocumentTypes
  }
}

query Demo($documentType: String) {
  demoByDocType: demo(
    filter: { onlyForDocumentTypes: { anyEqualTo: $documentType } }
  ) {
    ...Nodes
  }
  demoForAllDocTypes: demo(
    filter: { onlyForDocumentTypes: { isNull: true } }
  ) {
    ...Nodes
  }
}

[pixels-farm]

I can confirm it - OR filter doesn't work even for different fields - still getting AND instead :(

[Venryx]

I don't think you're using the or operator correctly. See here: #37 (comment)

The operators documentation page also hints at this. (it says "Checks for any expressions in this list.", and says it can be expressed in GraphQL as or: [T])

Looks like the correct way is something like:

query {
  allClients(filter: {
    or: [
      { sex: { in: [FEMALE] } },
      { sex: { isNull: true } }
    ]
  }) {
    nodes {
      id
      firstName
    }
  }
}

[evolkmann]

@Venryx thanks for that hint! It works correctly when I pass an array of filters. I did not discover this, because GraphiQL constructs an object when I select multiple filters.

[jcrben]

Ran into the same thing, I suppose that GraphiQL's construction is outside the scope of what is fixable here...