.NET 5 ADO.NET client wrong result for COALESCE

Can you check the REQUEST column in the QUERY_REQUESTS system table for the SQL that your ADO.net client is passing to Vertica?

Hi @Jim_Knicely,
the output from the REQUEST column for this query is:
SELECT COALESCE(null, '5') AS Result;

It seems that there is a problem with passing the correct value - it is somehow rounded, as for the following test:

    [Test]
    public async Task TestDirectlyFromConnectionNumeric()
    {
        await using var connection = VerticaHelper.OpenLocalVerticaDatabaseConnection();
        await using var command = connection.CreateCommand();
        command.CommandText = $"SELECT COALESCE(@a, @b) AS Result";
        command.Parameters.Add(new VerticaParameter("a", VerticaType.Numeric, 5.8m));
        command.Parameters.Add(new VerticaParameter("b", VerticaType.Numeric, 5.3m));

        DbDataReader reader = await command.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            decimal value = reader.GetDecimal(0);
            value.Should().Be(5.8m);
        }
    }

Result in value 6m: "Expected value to be 5.8M, but found 6M.".

The REQUEST column here reveals: SELECT COALESCE('6', '5') AS Result;

But again, when run directly query SELECT COALESCE('5.8000'::DECIMAL(19,4), '5.3000'::DECIMAL(19,4))
result is as expected: 5.8.

Apparently, there must be something wrong with the parameter type casting.

kind regards,
Kamil

What happens if you do this?

command.Parameters.Add(new VerticaParameter( "a", VerticaType.VarChar));
command.Parameters["a"] = 5.8;
command.Parameters.Add(new VerticaParameter( "b", VerticaType.VarChar));
command.Parameters["b"] = 5.3;

@Jim_Knicely thanks for the nice workaround.
Small adjustment: it is impossible to call command.Parameters["a"] = 5.8; Only assignment by Value is allowed.
Including what's above I tried this:

[Test]
public async Task TestDirectlyFromConnectionDecimal2()
{
await using var connection = VerticaHelper.OpenLocalVerticaDatabaseConnection();
await using var command = connection.CreateCommand();
command.CommandText = $"SELECT COALESCE(@a, @b) AS Result";
command.Parameters.Add(new VerticaParameter("a", VerticaType.VarChar)
{
Value = 5.8
});
command.Parameters.Add(new VerticaParameter("b", VerticaType.VarChar)
{
Value = 5.1
});

        DbDataReader reader = await command.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            // Generated query: SELECT COALESCE('5,8', '5,1') AS Result;
            var decimalValue = reader.GetDecimal(0);
            decimalValue.Should().Be(5.8m);
            var value = reader.GetValue(0);
            value.Should().Be(5.8m);
        }
    }

Small caveat: I need to know the type upfront, otherwise the second assertion would fail: "Expected value to be 5.8M, but found "5,8".

All fine, BUT this is providing that the function being called can handle any type, but when it comes to the specific type parameter function like ABS - this concept just fails.
Have a look a these two tests:

[Test]
public async Task TestDirectlyFromConnectionABSVarchar()
{
await using var connection = VerticaHelper.OpenLocalVerticaDatabaseConnection();
await using var command = connection.CreateCommand();
command.CommandText = $"SELECT ABS(@a) AS Result";
command.Parameters.Add(new VerticaParameter("a", VerticaType.VarChar)
{
Value = -5.8
});

        DbDataReader reader = await command.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            var decimalValue = reader.GetDecimal(0);
            decimalValue.Should().Be(5.8m);
        }
    }

    [Test]
    public async Task TestDirectlyFromConnectionABSNumeric()
    {
        await using var connection = VerticaHelper.OpenLocalVerticaDatabaseConnection();
        await using var command = connection.CreateCommand();
        command.CommandText = $"SELECT ABS(@a) AS Result";
        command.Parameters.Add(new VerticaParameter("a", VerticaType.Numeric)
        {
            Value = -5.8
        });

        DbDataReader reader = await command.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            var decimalValue = reader.GetDecimal(0);
            decimalValue.Should().Be(5.8m);
        }
    }

Both of them returns the following stack:
Vertica.Data.VerticaClient.VerticaException : [42725] ERROR: Function ABS(unknown) is not unique
----> Vertica.Data.Internal.Util.ServerException : ERROR: Function ABS(unknown) is not unique
at Vertica.Data.Internal.ADO.Net.SCommand.Prepare(Boolean isExecDirect)
at Vertica.Data.Internal.ADO.Net.SCommand.ExecuteReader(CommandBehavior behavior)
at Vertica.Data.Internal.ADO.Net.SCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
The same I would receive if I ran query:

SELECT ABS('-5.8') AS Result

Hi @Hibiki,
surprisingly this is working like a charm. Thank you for your support.