.NET 5 ADO.NET client wrong result for COALESCE

kamil_kliczborkamil_kliczbor Community Edition User
edited July 2021 in General Discussion

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?

Best Answer

  • HibikiHibiki Vertica Employee Employee
    Answer ✓

    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);
        }
    }
    

Answers

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    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_kliczborkamil_kliczbor Community Edition User
    edited July 2021

    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_KnicelyJim_Knicely - Select Field - Administrator

    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_kliczborkamil_kliczbor Community Edition User

    @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_kliczborkamil_kliczbor Community Edition User

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file