Broken type mapping on nullable structs (discovered for npgsql)

[FLAMESpl]

Consider this application:

    class Program
    {
        static async Task Main(string[] args)
        {
            const string connectionString = "User ID=postgres;Password=postgres;Host=localhost;Port=5432;Database=ac;";
            await using var connection = new NpgsqlConnection(connectionString);

            DateTime? param = null;
            await connection.QueryAsync(
                "SELECT 1 WHERE @param IS NOT NULL",
                new
                {
                    param
                });

            Console.WriteLine("Success");
        }
    }

in version 2.0.90 of Dapper there were no issues with such query, in version 2.0.123 postgres returns error 42P18 which stands for ambiguous_parameter

Unhandled exception. Npgsql.PostgresException (0x80004005): 42P18: could not determine data type of parameter $1
   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 Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 418
   at NpgsqlDateTimeBug.Program.Main(String[] args) in C:\Users\lszafirski\source\repos\NpgsqlDateTimeBug\NpgsqlDateTimeBug\Program.cs:line 17
   at NpgsqlDateTimeBug.Program.Main(String[] args) in C:\Users\lszafirski\source\repos\NpgsqlDateTimeBug\NpgsqlDateTimeBug\Program.cs:line 24
   at NpgsqlDateTimeBug.Program.<Main>(String[] args)
  Exception data:
    Severity: ERROR
    SqlState: 42P18
    MessageText: could not determine data type of parameter $1
    File: postgres.c
    Line: 1499
    Routine: exec_parse_message

I think the type of parameter in prepared statement is unknown If I change parameter type to default non-nullable DateTime or string error does not appear.

Target framework: net5.0 Npgsql: 5.0.11 Postgres: PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

[mgravell]

This is specific to DateTime and TimeSpan IIRC; @roji - any suggestions or advice here? if I do include the DB types, I make NPGSQL unhappy; if I don't include the DB types, I make NPGSQL unhappy...

[roji]

Yeah, this can occur in various other contexts where the SQL doesn't contain enough information to infer the type. I definitely don't think it should be a reason for Dapper to include the DB type.

Some options:

• Type the parameter in SQL (e.g. @param::text)
• Use a Dapper custom type handler to set the DbType (or NpgsqlDbType)
• Refrain from sending a parameter to the database only for it to be checked for null (this is frequently questionable). For example, you could check it client-side and execute two different queries, or check it client-side and send the bool result of whether it's null or not in your query.

[FLAMESpl]

I will stick to data type casting. 4 parameters already requires 16 different queries and I would like to avoid building query during request for readability and performance reasons.

[roji]

Sure thing - whatever works for you.

For context, this is ultimately a result of some serious timestamp-related breaking changes in Npgsql v6.0, which Dapper reacted to in #1716 by no longer setting DbType on DateTime parameters. That means that Npgsql is left to its own to infer the PG type, but when it's given a null it cannot do that.

This problem already existed before all this happened, e.g. when trying to send any PG-specific type which Dapper doesn't know about, and which doesn't even have a DbType (e.g. IPAddress). The 6.0 change only extended that problem to DateTimes.