Orchardcore: Cannot using Where query from GraphQL because columns case

Created on 22 Apr 2020 · 19Comments · Source: OrchardCMS/OrchardCore

It's happened with the default parts (Autoroute, Alias, Localization) with postgres@10 and OC dev channel.
Somewhat related to this https://github.com/OrchardCMS/OrchardCore/issues/3684
For e.g.:

query MyQuery {
  blogPost(where: { path: "some-path" }) {
    displayText
  }
}

It throws this error:

GraphQL.ExecutionError: Error trying to resolve something.\n ---> Npgsql.PostgresException (0x80004005): 42703: column AutoroutePartIndex.path does not exist\n at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<g__ReadMessageLong|0>d.MoveNext()\n--- End of stack trace from previous location where exception was thrown ---\n at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<g__ReadMessageLong|0>d.MoveNext()\n--- End of stack trace from previous location where exception was thrown ---\n at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)\n at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)\n at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)\n at Dapper.SqlMapper.QueryAsyncT in C:\projects\dapper\Dapper\SqlMapper.Async.cs:line 419\n at YesSql.Store.ProduceAsync[T](WorkerQueryKey key, Func2 work, Object[] args)\n at YesSql.Services.DefaultQuery.Query1.ListImpl()\n at OrchardCore.ContentManagement.GraphQL.Queries.ContentItemsFieldType.Resolve(ResolveFieldContext context) in C:\projects\orchardcore\src\OrchardCore\OrchardCore.ContentManagement.GraphQL\Queries\ContentItemsFieldType.cs:line 104\n at OrchardCore.Apis.GraphQL.Resolvers.LockedAsyncFieldResolver`1.Resolve(ResolveFieldContext context) in C:\projects\orchardcore\src\OrchardCore\OrchardCore.Apis.GraphQL.Abstractions\Resolvers\LockedAsyncFieldResolver.cs:line 25\n at GraphQL.Execution.ExecutionStrategy.ExecuteNodeAsync(ExecutionContext context, ExecutionNode node)\n Exception data:\n Severity: ERROR\n SqlState: 42703\n MessageText: column AutoroutePartIndex.path does not exist\n Hint: Perhaps you meant to reference the column \"AutoroutePartIndex.Path\".\n Position: 371\n File: parse_relation.c\n Line: 3283\n Routine: errorMissingColumn\n --- End of inner exception stack trace ---

      {
        "code": "POSTGRES",
        "data": {
          "Severity": "ERROR",
          "InvariantSeverity": "ERROR",
          "SqlState": "42703",
          "MessageText": "column AutoroutePartIndex.path does not exist",
          "Hint": "Perhaps you meant to reference the column \"AutoroutePartIndex.Path\".",
          "Position": 371,
          "File": "parse_relation.c",
          "Line": "3283",
          "Routine": "errorMissingColumn"
        }
      }

Should I add an alias here for the column name:
https://github.com/OrchardCMS/OrchardCore/blob/cc53e491f9747277813ca2f72f42b5396cbe6d04/src/OrchardCore.Modules/OrchardCore.Autoroute/GraphQL/AutoroutePartIndexAliasProvider.cs#L9-L17
Or look at this function for clues: https://github.com/OrchardCMS/OrchardCore/blob/cc53e491f9747277813ca2f72f42b5396cbe6d04/src/OrchardCore/OrchardCore.ContentManagement.GraphQL/Queries/Predicates/PredicateQuery.cs#L55-L65

Thanks,

GraphQL bug

Source

trungtin

All 19 comments

Case sensitivity issue with PostgreSQL.
Could you try, just for the records, this instead :

query MyQuery {
  blogPost(where: { Path: "some-path" }) {
    displayText
  }
}

Skrypt on 25 Apr 2020

Yeah I understand that it's from case-sensitivity with PostgreSQL, evidently from this line https://github.com/OrchardCMS/OrchardCore/blob/cc53e491f9747277813ca2f72f42b5396cbe6d04/src/OrchardCore/OrchardCore.ContentManagement.GraphQL/Queries/Predicates/PredicateQuery.cs#L89
this Dialect.QuoteForColumnName(values[1]) return "Path"

Your example doesn't work because of graphql validation error ;)

trungtin on 25 Apr 2020

👍1

urg; postgres case-sensitivity again arg ;p will think on this some.

carlwoodhouse on 28 May 2020

@carlwoodhouse we are also facing the same issue with the Postgres database. Will it is fixed soon or it is better to switch to MS SQL or Mysql database

qadirkanore on 4 Jun 2020

Ok one part of the issue is that in PostgreSQL table and column name are case sensitive when they are double quoted.

Example SELECT * FROM "Foo"

Whereas unquoted names are always folded to lower case.
Because FOO, foo and "foo" are the same for PostgreSQL.
For that matter normally we should always use lower case names for table and column names in PostgreSQL.

https://github.com/sebastienros/yessql/blob/dev/src/YesSql.Provider.PostgreSql/PostgreSqlDialect.cs#L128

```C#
public override string QuoteForColumnName(string columnName)
{
return QuoteString + columnName.ToLower() + QuoteString;
}

    public override string QuoteForTableName(string tableName)
    {
        return QuoteString + tableName.ToLower() + QuoteString;
    }

```

Here, since the table name is passed in the GraphQL Query in lowercase and that our table names are Pascal case it won't find "Table.column" since it should be "Table.Column".

One solution would be to get the real table name from the database schema. But that would require an extra query just for that matter which is irrelevant.

So one solution is to make all table names and column names lowercase in PostgreSQL.

https://github.com/sebastienros/yessql/blob/dev/src/YesSql.Core/Sql/SchemaBuilder.cs
https://github.com/sebastienros/yessql/blob/dev/src/YesSql.Provider.Common/SqlBuilder.cs

I think this is an issue that could be fixed in YesSQL.
I've referenced those 2 classes above for reference as I'm not sure 100% that the SchemaBuilder and SqlBuilder will work properly after changing the PostgreSqlDialect class that way. It's worth a try.

So here an other point is the fact that GraphQL uses Camel Case on it's properties. Which I think fails validation when using "Path" instead of "path" on it's property name.

I will analyze this more tomorrow.

Skrypt on 4 Jun 2020

Quick first draft on this which works :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text.RegularExpressions;
using OrchardCore.ContentManagement.Records;
using OrchardCore.Environment.Shell;
using YesSql;

namespace OrchardCore.ContentManagement.GraphQL.Queries.Predicates
{
    public class PredicateQuery : IPredicateQuery
    {
        private static Dictionary<string, string> _contentItemIndexProperties = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
        private readonly HashSet<string> _usedAliases = new HashSet<string>();
        private readonly Dictionary<string, string> _aliases = new Dictionary<string, string>();
        private readonly string _tablePrefix;

        public PredicateQuery(ISqlDialect dialect, ShellSettings shellSettings)
        {
            Dialect = dialect;

            var tablePrefix = shellSettings["TablePrefix"];
            _tablePrefix = string.IsNullOrEmpty(tablePrefix) ? String.Empty : $"{tablePrefix}_";
        }

        public ISqlDialect Dialect { get; set; }

        public IDictionary<string, object> Parameters { get; } = new Dictionary<string, object>();

        static PredicateQuery()
        {
            foreach (var property in typeof(ContentItemIndex).GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase))
            {
                _contentItemIndexProperties[property.Name] = property.Name;
            }
        }

        public string NewQueryParameter(object value)
        {
            var count = Parameters.Count;
            var parameterName = $"@x{count + 1}";

            Parameters.Add(parameterName, value);

            return parameterName;
        }

        public void CreateAlias(string path, string alias)
        {
            if (path == null) throw new ArgumentNullException(nameof(path));
            if (alias == null) throw new ArgumentNullException(nameof(alias));

            _aliases[path] = alias;
        }

        public string GetColumnName(string propertyPath)
        {
            if (propertyPath == null) throw new ArgumentNullException(nameof(propertyPath));

            // Check if there's an alias for the full path
            // aliasPart.Alias -> AliasFieldIndex.Alias
            if (_aliases.TryGetValue(propertyPath, out string alias))
            {
                _usedAliases.Add(alias);
                return Dialect.QuoteForColumnName(alias);
            }

            var values = propertyPath.Split('.', 2);
            if (values.Length == 1)
            {
                if (_aliases.TryGetValue(string.Empty, out alias))
                {
                    // Return the default alias
                    // contentItemId -> ContentItemIndex.ContentItemId

                    if (_contentItemIndexProperties.TryGetValue(values[0], out var columnName))
                    {
                        _usedAliases.Add(alias);
                        return Dialect.QuoteForTableName($"{_tablePrefix}{alias}") + "." + Dialect.QuoteForColumnName(columnName);
                    }
                }
            }
            else
            {
                if (_aliases.TryGetValue(values[0], out alias))
                {
                    // Switch the given alias in the path with the mapped alias.
                    // aliasPart.Alias -> AliasPartIndex.Alias
                    _usedAliases.Add(alias);
                    return Dialect.QuoteForTableName($"{_tablePrefix}{alias}") + "." + Dialect.QuoteForColumnName(ToPascalCase(values[1]));
                }
            }

            // No aliases registered for this path, return the formatted path.
            return Dialect.QuoteForColumnName(propertyPath);
        }

        public IEnumerable<string> GetUsedAliases()
        {
            return _usedAliases;
        }

        public string ToPascalCase(string original)
        {
            Regex invalidCharsRgx = new Regex("[^_a-zA-Z0-9]");
            Regex whiteSpace = new Regex(@"(?<=\s)");
            Regex startsWithLowerCaseChar = new Regex("^[a-z]");
            Regex firstCharFollowedByUpperCasesOnly = new Regex("(?<=[A-Z])[A-Z0-9]+$");
            Regex lowerCaseNextToNumber = new Regex("(?<=[0-9])[a-z]");
            Regex upperCaseInside = new Regex("(?<=[A-Z])[A-Z]+?((?=[A-Z][a-z])|(?=[0-9]))");

            // replace white spaces with undescore, then replace all invalid chars with empty string
            var pascalCase = invalidCharsRgx.Replace(whiteSpace.Replace(original, "_"), string.Empty)
                // split by underscores
                .Split(new char[] { '_' }, StringSplitOptions.RemoveEmptyEntries)
                // set first letter to uppercase
                .Select(w => startsWithLowerCaseChar.Replace(w, m => m.Value.ToUpper()))
                // replace second and all following upper case letters to lower if there is no next lower (ABC -> Abc)
                .Select(w => firstCharFollowedByUpperCasesOnly.Replace(w, m => m.Value.ToLower()))
                // set upper case the first lower case following a number (Ab9cd -> Ab9Cd)
                .Select(w => lowerCaseNextToNumber.Replace(w, m => m.Value.ToUpper()))
                // lower second and next upper case letters except the last if it follows by any lower (ABcDEf -> AbcDef)
                .Select(w => upperCaseInside.Replace(w, m => m.Value.ToLower()));

            return string.Concat(pascalCase);
        }
    }
}

Skrypt on 4 Jun 2020

Maybe we could just store the original casing of the field in the input on metadata in graphql and pass that, incase someone is crazy and has all upper case letters in their index names or something weird :p not sure.