Parameters name conflict when using Expressions with PostgreSQL

[Usaga]

Good day!

It seems there is some kind of query parameters name conflict when heavelly using Expression. I used parameters userId and now in expression and helpers method and I got exception:

Exception message:
`Npgsql.PostgresException: '42601: syntax error at or near ":"`

When I change params to userId2 and now2 in Expression or GetQuery method this starts to work.

Current workaround for me: ensure all parameters have unique names. This is not robust solution, but for a moment it works.

Steps to reproduce

using System.Linq.Expressions;

using LinqToDB;
using LinqToDB.Data;
using LinqToDB.DataProvider.PostgreSQL;
using LinqToDB.Mapping;

static Expression<Func<MediaItem, MediaItemSearchSharedResult>> MediaItemSearchSharedResultProjection(DateTime now)
{
   // If change now -> now2 query will work
    return x => new MediaItemSearchSharedResult
    {
        CategoryIds = x.Categories.Select(y => y.CategoryId).ToList(),
        IsUnvisited = x.UserShare.Any(y => y.ExpiresAt > now)
    };
}

static IQueryable<MediaItem> GetQuery(DbContext context, Guid userId, DateTime now)
{
    return context.GetTable<MediaItem>()
        .Where(x =>
            x.UserShare.Any(y => y.UserId == userId && y.ExpiresAt > now) ||
            x.UserShare.Any(y => y.CreatedById == userId && y.ExpiresAt > now));
}

var options = new DataOptions()
    .UsePostgreSQL("connection string", PostgreSQLVersion.v15)
    .UseMappingSchema(LinqToDB.Mapping.MappingSchema.Default);

{
    using var context = new DbContext(options);

    var now = DateTime.Now;
    var userId = Guid.NewGuid();

    var result = GetQuery(context, userId, now)
            .Select(MediaItemSearchSharedResultProjection(now))
            .ToList();
}

{
    using var context = new DbContext(options);

    var now = DateTime.Now;
    var userId = Guid.NewGuid();

    var result = GetQuery(context, userId, now)
            .Select(MediaItemSearchSharedResultProjection(now))
            .ToList();
}

Console.WriteLine("Done");

[Table("media_item_to_media_item_categories")]
internal sealed class MediaItemToMediaItemCategory
{
    [Column("id")]
    [PrimaryKey]
    public Guid Id { get; set; }

    [Column("category_id")]
    public Guid CategoryId { get; set; }

    [Column("media_item_id")]
    public Guid MediaItemId { get; set; }
}

[Table("media_item_user_share")]
internal sealed class MediaItemUserShare
{
    [Column("id")]
    [PrimaryKey]
    public Guid Id { get; set; }

    [Column("media_item_id")]
    public Guid MediaItemId { get; set; }

    [Column("created_by_id")]
    public Guid CreatedById { get; set; }

    [Column("user_id")]
    public Guid UserId { get; set; }

    [Column("expires_at")]
    public DateTime ExpiresAt { get; set; }
}

[Table("media_items")]
internal sealed class MediaItem
{
    [Column("id")]
    [PrimaryKey]
    public Guid Id { get; set; }

    [Association(ThisKey = nameof(Id), OtherKey = nameof(MediaItemToMediaItemCategory.MediaItemId))]
    public IList<MediaItemToMediaItemCategory> Categories { get; set; } = new List<MediaItemToMediaItemCategory>();

    [Association(ThisKey = nameof(Id), OtherKey = nameof(MediaItemUserShare.MediaItemId))]
    public IList<MediaItemUserShare> UserShare { get; set; } = new List<MediaItemUserShare>();
}

public sealed class MediaItemSearchSharedResult
{
    public IList<Guid> CategoryIds { get; set; } = new List<Guid>();

    public bool IsUnvisited { get; set; }
}

internal sealed class DbContext : DataConnection
{
    public DbContext(DataOptions options) : base(options)
    {
        (this as IDataContext).CloseAfterUse = true;

        CommandTimeout = (int)TimeSpan.FromMinutes(5).TotalSeconds;
    }
}

Wrong SQL seen in logs:

2023-08-22 16:34:08.050 +07 [17180] ERROR:  syntax error at or near ":" at character 281
2023-08-22 16:34:08.050 +07 [17180] STATEMENT:  SELECT

		key_data_result.id,

		detail.category_id

	FROM

		(

			SELECT DISTINCT

				x.id

			FROM

				media_items x

			WHERE

				(EXISTS(

					SELECT

						*

					FROM

						media_item_user_share y

					WHERE

						x.id = y.media_item_id AND y.user_id = $1 AND

						y.expires_at > :now

				) OR EXISTS(

					SELECT

						*

					FROM

						media_item_user_share y_1

					WHERE

						x.id = y_1.media_item_id AND y_1.created_by_id = $1 AND

						y_1.expires_at > :now

				))

		) key_data_result

			INNER JOIN media_item_to_media_item_categories detail ON key_data_result.id = detail.media_item_id

Environment details

Linq To DB version: 5.2.2

Database (with version): PostgreSQL 15

ADO.NET Provider (with version): Npgsql 7.0.4

Operating system: Windows 10

.NET Version: 7.0

[MaceWindu]

It looks like log on server-side (I see positional parameters like $1).
Do you have client-side query from linq2db logs?

[Usaga]

It's pretty much the same:

BeforeExecute
--  PostgreSQL.15 PostgreSQL
DECLARE @userId Uuid -- Guid
SET     @userId = '1e81691f-8bbe-495b-89ca-b28ace9c7a7f'::uuid
DECLARE @now_1 Timestamp -- DateTime2
SET     @now_1 = '2023-08-23 09:30:34.265'::timestamp

SELECT
        key_data_result.id,
        detail.category_id
FROM
        (
                SELECT DISTINCT
                        x.id
                FROM
                        media_items x
                WHERE
                        (EXISTS(
                                SELECT
                                        *
                                FROM
                                        media_item_user_share y
                                WHERE
                                        x.id = y.media_item_id AND y.user_id = :userId AND
                                        y.expires_at > :now
                        ) OR EXISTS(
                                SELECT
                                        *
                                FROM
                                        media_item_user_share y_1
                                WHERE
                                        x.id = y_1.media_item_id AND y_1.created_by_id = :userId AND
                                        y_1.expires_at > :now
                        ))
        ) key_data_result
                INNER JOIN media_item_to_media_item_categories detail ON key_data_result.id = detail.media_item_id

Error
Exception: Npgsql.PostgresException
Message  : 42601: syntax error at or near ":"

At the first SQL (from server's log) I see named parameter :now which should not be there. As far as I know, Npgsl should replace it with positional one, but didn't for some reason.

Second thing, which I noticed is that problem rises at second query execution. At first one all works fine. Is there some cache involved?

[MaceWindu]

This should be fixed by #4230

Second thing, which I noticed is that problem rises at second query execution. At first one all works fine. Is there some cache involved?

yes, it happens to cached queries, but in your case it leads to incorrect parameter names for some reason...