Missing parentheses in query translation (NpgsqlTsVector Matches)

[lesley29]

Hi!

After migrating to .NET5 I upgraded Npgsql.EntityFrameworkCore.PostgreSQL to 5.0.1 and Npgsql to 5.0.1.1 and ran into the issue with full-text search query translation.

Could you please take a look at the following toy example:

using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace Sandbox
{
    class Program
    {
        static async Task Main(string[] args)
        {
            await using var ctx = new SomeDbContext();

            var result = await ctx.Models
                .Where(m => EF.Functions.ToTsVector(m.SomeProp)
                    .Matches(EF.Functions
                        .PlainToTsQuery("english", "some text")
                        .Or(EF.Functions.PlainToTsQuery("english", "some other text"))
                    )
                )
                .ToListAsync();
        }
    }

    public class SomeDbContext : DbContext
    {
        public DbSet<SomeModel> Models { get; set; }

        private static ILoggerFactory ContextLoggerFactory
            => LoggerFactory.Create(b => b.AddConsole().AddFilter("", LogLevel.Information));

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder
                .UseNpgsql(@"Host=localhost;Port=6432;Database=test;Username=postgres;Password=example")
                .UseSnakeCaseNamingConvention()
                .EnableSensitiveDataLogging()
                .UseLoggerFactory(ContextLoggerFactory);
    }

    [Table("model")]
    public class SomeModel
    {
        public int Id { get; set; }

        public string SomeProp { get; set; }

        public string SomeOtherProp { get; set; }
    }
}

/*
    CREATE DATABASE test;

    CREATE TABLE model(
        id int PRIMARY KEY,
        some_prop varchar NOT NULL,
        some_other_prop varchar NOT NULL
    );
*/

The previous query translates into the following SQL:

 SELECT m.id, m.some_other_prop, m.some_prop
      FROM model AS m
      WHERE to_tsvector(m.some_prop) @@ plainto_tsquery('english', 'some text') || plainto_tsquery('english', 'some other text')

and I get an error like:

 Npgsql.PostgresException (0x80004005): 42883: operator does not exist: boolean || tsquery
         at Npgsql.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|194_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
         at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
         at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
        Exception data:
          Severity: ERROR
          SqlState: 42883
          MessageText: operator does not exist: boolean || tsquery
          Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
          Position: 135
          File: parse_oper.c
          Line: 728
          Routine: op_error

However, in the previous version (Npgsql 4.1.7 & Npgsql.EntityFrameworkCore.PostgreSQL 3.1.4), it works just fine and translates into

 SELECT m.id, m.some_other_prop, m.some_prop
      FROM model AS m
      WHERE (to_tsvector(m.some_prop) @@ (plainto_tsquery('english', 'some text') || plainto_tsquery('english', 'some other text')))

Could you please point me out, am I missing something? or maybe some behaviour has changed?

[roji]

Thanks, I can indeed see this happening - will investigate.