GraphQL error when filer on field that exists in two or more more types

[AndreySurkov]

Describe the bug

When cteate two ContentTypes with field that named similar but with different type, GraphQL throws error when filter by this field.

GraphQL.Execution.UnhandledError: Error trying to resolve field 'numberType'.
 ---> Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'no such column: Text'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements()+MoveNext()
   at Microsoft.Data.Sqlite.SqliteCommand.GetStatements()+MoveNext()
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 434
   at YesSql.Store.<>c__DisplayClass44_0`2.<<ProduceAwaitedAsync>b__0>d.MoveNext()
--- End of stack trace from previous location ---
   at YesSql.Data.WorkDispatcher`2.ScheduleAsync[TState](TKey key, TState state, Func`3 valueFactory)
   at YesSql.Store.ProduceAwaitedAsync[T,TState](WorkerQueryKey key, Func`3 work, TState state)
   at YesSql.Services.DefaultQuery.Query`1.ListImpl()
   at YesSql.Services.DefaultQuery.Query`1.ListImpl()\
   at OrchardCore.ContentManagement.GraphQL.Queries.ContentItemsFieldType.ResolveAsync(IResolveFieldContext context) in C:\\Repo\\GitHub\\OrchardCore\\src\\OrchardCore\\OrchardCore.ContentManagement.GraphQL\\Queries\\ContentItemsFieldType.cs:line 116
   at GraphQL.Resolvers.FuncFieldResolver`1.<>c__DisplayClass2_0.<<-ctor>b__0>d.MoveNext() in /_/src/GraphQL/Resolvers/FuncFieldResolver.cs:line 36
--- End of stack trace from previous location ---
   at GraphQL.Execution.ExecutionStrategy.ExecuteNodeAsync(ExecutionContext context, ExecutionNode node) in /_/src/GraphQL/Execution/ExecutionStrategy.cs:line 516
   --- End of inner exception stack trace ---"

Orchard Core version

2.1.0

To Reproduce

1. Enable Content Fields Indexing (SQL) feature
2. Create ContentType "NumberType"
   • Add Number Field wih name Value
3. Create ContentType "StringType"
   • Add TExt Field wih name Value
4. Go To GraphQL and run query

query MyQuery {
  numberType(where: {value: 123}) {
    value
  }
}

Logs and screenshots

When only "NumberType" created

When "NumberType" and "StringType" created

[sebastienros]

I would love to see the generated SQL to understand what went wrong. It's definitely looking at the wrong table, maybe it's just missing a predicate on the index type.

[github-actions]

We triaged this issue and set the milestone according to the priority we think is appropriate (see the docs on how we triage and prioritize issues).

This indicates when the core team may start working on it. However, if you'd like to contribute, we'd warmly welcome you to do that anytime. See our guide on contributions here.

[gvkries]

I think this happens, because the DynamicContentFieldsIndexAliasProvider creates ambiguous alias names. Only one alias is then taken into account, which is the latest one being found.

OrchardCore/src/OrchardCore/OrchardCore.ContentManagement.GraphQL/Queries/Types/DynamicContentFieldsIndexAliasProvider.cs

Line 47 in 10c4319

var alias = _contentOptions.ShouldCollapse(part) ?

This needs to change, also how the aliases are used in the ContentItemsFieldType.FilterWhereArgumentsAsync() method.

OrchardCore/src/OrchardCore/OrchardCore.ContentManagement.GraphQL/Queries/ContentItemsFieldType.cs

Line 156 in 10c4319

predicateQuery.CreateAlias(alias.Alias, alias.Index);

@mdameer I think you built this functionality, can you have a look?

[gvkries]

For completeness, here is the generated SQL for the following query:

query MyQuery {
  numberType(where: {value: 123}) {
    value
  }
  stringType(where: {value: "Text123"}) {
    value
  }
}

For numberType, which uses the wrong index:

SELECT [Document].* FROM [Document] 
  INNER JOIN (
    SELECT [Document].[Id], ContentItemIndex_a1.[CreatedUtc] AS order_1 FROM [Document] 
      INNER JOIN [ContentItemIndex] AS ContentItemIndex_a1 ON ContentItemIndex_a1.[DocumentId] = [Document].[Id] 
      INNER JOIN [TextFieldIndex] AS TextFieldIndex_a1 ON TextFieldIndex_a1.[DocumentId] = [Document].[Id] 
        WHERE [Document].[Type] = @Type AND  ((ContentItemIndex_a1.[Published] = @p1) AND (ContentItemIndex_a1.[ContentType] = @p2) AND (([Text] = @x1))) 
        GROUP BY [Document].[Id] ORDER BY order_1 DESC LIMIT 100
  ) AS IndexQuery ON IndexQuery.[Id] = [Document].[Id] ORDER BY order_1 DESC

For the stringType:

SELECT [Document].* FROM [Document] 
  INNER JOIN (
    SELECT [Document].[Id], ContentItemIndex_a1.[CreatedUtc] AS order_1 FROM [Document] 
      INNER JOIN [ContentItemIndex] AS ContentItemIndex_a1 ON ContentItemIndex_a1.[DocumentId] = [Document].[Id] 
      INNER JOIN [TextFieldIndex] AS TextFieldIndex_a1 ON TextFieldIndex_a1.[DocumentId] = [Document].[Id] 
        WHERE [Document].[Type] = @Type AND  ((ContentItemIndex_a1.[Published] = @p1) AND (ContentItemIndex_a1.[ContentType] = @p2) AND (([Text] = @x1))) 
        GROUP BY [Document].[Id] ORDER BY order_1 DESC LIMIT 100
  ) AS IndexQuery ON IndexQuery.[Id] = [Document].[Id] ORDER BY order_1 DESC

Additionally, the table aliases are not used at all.