.NET 5 ADO.NET client wrong result for COALESCE

kamil_kliczbor

Hi,
I'm using ADO.NET client 10.1.x under .NET 5 windows app. Can anyone explain why there is a mismatch for the returned type reading the result from client and directly from Vertica?

When executing the query
"SELECT COALESCE(NULL::int, 5.21::numeric) AS Result" i receive result 5.21 as expected. (the client is DBeaver).

But when i try connect from code:

[Test]
public async Task TestDirectlyFromConnection()
{
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.BigInt, DBNull.Value));
command.Parameters.Add(new VerticaParameter("b", VerticaType.Numeric, 5.21m));

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

I get the following error: "Expected value to be 5.21M, but found 5M."

Can anyone tell me what is wrong, or maybe it is a bug?

Hibiki

Hi @kamil_kliczbor,

When executing your original code, the following warning happens. That is why you receive the truncated value.

[20070] Fractional value '5.21' truncated when converting to string.

This is because it uses the max number of digits for precision and scale if these are not specified, then it seems that max number hits the limitation for the length of one column value when communicating with the server.

Please try to execute the following code and see if it works.

[Test]
public async Task TestDirectlyFromConnection()
{
    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.BigInt, DBNull.Value));
    command.Parameters.Add(new VerticaParameter("b", VerticaType.Numeric));
    command.Parameters["b"].Value = 5.21m;
    command.Parameters["b"].Precision = 3;
    command.Parameters["b"].Scale = 2;

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

Jim_Knicely

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

kamil_kliczbor

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

Jim_Knicely

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;

kamil_kliczbor

@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

kamil_kliczbor

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