Marten 7 Command Failure: 42P08: could not determine data type of parameter $1

[murlakatam]

Executing raw sql

Task<IReadOnlyList<T>> QueryAsync<T>(
      string sql,
      CancellationToken token,
      params object[] parameters)

on IQuerySession

  var results = await session.QueryAsync<SomeDto>(
            """
            select to_json(t)
            from (select r.data ->> 'Status' Status, count(distinct(r.data ->> 'Id')) Count
                  from mt_doc_someprojection r
                  
                  where 1 = 1
                    and (:serviceId is null or r.data ->> 'ServiceId' = :serviceId)
                  group by r.data ->> 'Status') t
            """, cancellationToken, new
            {
                serviceId = string.IsNullOrEmpty(findRequestsDto.ServiceId) ? null : findRequestsDto.ServiceId
            });
        return results;

fails in Marten7

Npgsql.PostgresException (0x80004005): 42P08: could not determine data type of parameter $1

POSITION: 173
   at Npgsql.Internal.NpgsqlConnector.ReadMessageLong(Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder`1.StateMachineBox`1.System.Threading.Tasks.Sources.IValueTaskSource<TResult>.GetResult(Int16 token)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
   at Marten.Internal.Sessions.AutoClosingLifetime.ExecuteReaderAsync(NpgsqlBatch batch, CancellationToken token)
   at Marten.Internal.Sessions.AutoClosingLifetime.ExecuteReaderAsync(NpgsqlBatch batch, CancellationToken token)
   at Marten.Linq.MartenLinqQueryProvider.ExecuteHandlerAsync[T](IQueryHandler`1 handler, CancellationToken token)
  Exception data:
    Severity: ERROR
    SqlState: 42P08
    MessageText: could not determine data type of parameter $1
    Position: 173
    File: parse_param.c
    Line: 311
    Routine: check_parameter_resolution_walker

, but works fine in Marten6

if you remove :serviceId is null or bit the query is running fine.

[murlakatam]

Reproduced on latest 7.2.0
One workaround I found is not to use named parameters (via anonymous object) and duplicate values through ? placeholder
which we were trying to avoid with anonymous object in the first place

// we can't pass the input as named parameters until the https://github.com/JasperFx/marten/issues/3045 is fixed. until then we have to duplicate the params 
        var input = new
        {
            serviceId = string.IsNullOrEmpty(findRequestsDto.ServiceId) ? null : findRequestsDto.ServiceId,
        };

        var results = await session.QueryAsync<SomeDto>(
            """
            select to_json(t)
            from (select r.data ->> 'Status' Status, count(distinct(r.data ->> 'Id')) Count
                  from mt_doc_someprojection r
                  where 1 = 1
                    and (? is null or r.data ->> 'ServiceId' = ?)
                  group by r.data ->> 'Status') t
            """, cancellationToken,
            input.serviceId!, input.serviceId!
        );
        return results;

[jeremydmiller]

@murlakatam I think at most this one is going to be a documentation issue or an assertion to not use nulls for the named arguments like that, because there's no way for Npgsql or Marten to know what DbType to use for that parameter

[jeremydmiller]

Meh, it's going to take digging into Weasel and that just doesn't sound fun on a Friday. Next week.

[murlakatam]

Found a more elegant workaround, using params array and ? placeholder, that doesn't require duplication.

var results = await session.QueryAsync<SomeDto>(
            """
            with config as (select ? service_id)
            select to_json(t)
            from (select r.data ->> 'Status' Status, count(distinct (r.data ->> 'Id')) Count
                  from config c
                           cross join mt_doc_someprojection r
                  where 1 = 1
                    and (c.service_id is null or r.data ->> 'ServiceId' = c.service_id)
                  group by r.data ->> 'Status') t
            """, cancellationToken,
            input.serviceId! // nullable parameter
        );

[jeremydmiller]

@murlakatam I'm feeling dumb here, what's different?

[murlakatam]

@jeremydmiller do you mean what's different between two workarounds?

1. first one has to supply two duplicate params to achieve what's was possible with a single named param before v7. See the input.serviceId!, input.serviceId!. Our real query is way more complex with many nullable params in where clause.
   So duplication was an issue for us as it was exploding the number of anonymous params from 7 to 14
2. second workaround uses just a single anonymous param input.serviceId! // nullable parameter and achieves the same