Potgres with GraphQL: Invalid reference to FROM-clause entry for table ContentItemIndex

[anton-martyniuk]

I am using Orchard Core with Potgres DB (version 15.4 latest) and GraphQL enabled. I in the postgres db I have a custom schema for orchard. I have created a content type "blogPostCategory" and here is my GraphQL query:

blogPostCategory(where: { contentItemId: "12345" }) {
    contentItemId
    displayText
    slug
}

When executing it I get an the following error:

"message": "GraphQL.Execution.UnhandledError: Error trying to resolve field 'blogPostCategory'.\r\n ---> Npgsql.PostgresException
(0x80004005): 42P01: invalid reference to FROM-clause entry for table \"ContentItemIndex_a1\"\r\n\r\nPOSITION: 515\r\n   at 
Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|222_0(NpgsqlConnector connector, Boolean async, 
DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)\r\n   at 
Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)\r\n   at 
Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)\r\n   at 
Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)\r\n   at 
Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)\r\n   at 
Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)\r\n   at 
Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in 
/_/Dapper/SqlMapper.Async.cs:line 418\r\n   at YesSql.Store.ProduceAsync[T,TState](WorkerQueryKey key, Func`2 work, TState 
state)\r\n   at YesSql.Services.DefaultQuery.Query`1.ListImpl()\r\n   at YesSql.Services.DefaultQuery.Query`1.ListImpl()\r\n   at 
OrchardCore.ContentManagement.GraphQL.Queries.ContentItemsFieldType.Resolve(IResolveFieldContext context)\r\n   at 
OrchardCore.Apis.GraphQL.Resolvers.LockedAsyncFieldResolver`1.Resolve(IResolveFieldContext context)\r\n   at 
GraphQL.Execution.ExecutionStrategy.ExecuteNodeAsync(ExecutionContext context, ExecutionNode node) in 
/_/src/GraphQL/Execution/ExecutionStrategy.cs:line 396\r\n  Exception data:\r\n    Severity: ERROR\r\n    SqlState: 42P01\r\n    
MessageText: invalid reference to FROM-clause entry for table \"ContentItemIndex_a1\"\r\n    Hint: There is an entry for table 
\"ContentItemIndex_a1\", but it cannot be referenced from this part of the query.\r\n    Position: 515\r\n    File: parse_relation.c\r\n    
Line: 3574\r\n    Routine: errorMissingRTE\r\n   --- End of inner exception stack trace ---",
      "locations": [
        {
          "line": 2,
          "column": 3
        }
      ],
      "path": [
        "blogPostCategory"
      ],
      "extensions": {
        "code": "POSTGRES",
        "codes": [
          "POSTGRES"
        ],
        "data": {
          "Severity": "ERROR",
          "InvariantSeverity": "ERROR",
          "SqlState": "42P01",
          "MessageText": "invalid reference to FROM-clause entry for table \"ContentItemIndex_a1\"",
          "Hint": "There is an entry for table \"ContentItemIndex_a1\", but it cannot be referenced from this part of the query.",
          "Position": 515,
          "File": "parse_relation.c",
          "Line": "3574",
          "Routine": "errorMissingRTE"
        }
      }
    }
  ]

I found other issues with postgres like this old one: https://github.com/OrchardCMS/OrchardCore/issues/3684 But my error is another. I also tried wil older postgres db versions, still the same behaviour.

Steps to reproduce:

1. Create asp.net core 7 project
2. Add nuget package "OrchardCore.Application.Cms.Targets" version 1.6.0
3. Add usual orchard core registration code in Program.cs
4. Run the application and setup orchard using Postgres DB 15.4 and set the db schema, for example, to "foo-bar" (I have my schema with hyphen). Select "headless" as type of the project
5. In the orchard core admin panel enabled GraphQL feature (if it's not already enabled)
6. Create own content type, for example, "BlogPostCategory" add title (display text property) and text field "slug"
7. In the security settings allow listing "BlogPostCategory" for anynomous role and allow to query data using GraphQL
8. Open GraphiQL in orchard admin and write a query for blogPostCategory as mentioned earlier with where clause for "contentItemId"
9. Execute query and received an error.

Important: where clause for GraphQL fails for any property of any content object, even built-in ones like menu

[hishamco]

Could you please write down the steps to reproduce?

[anton-martyniuk]

Could you please write down the steps to reproduce?

Yes, I have updated my original comment

[anton-martyniuk]

I have tried the exact setup on MySQL database and all works fine. So the issue is only with Postgres