How to pass ADO.NET VerticaType.Time and avoid IndexOutOfRangeException ?

kamil_kliczbor

My question relates to the post https://forum.vertica.com/discussion/242318/net-5-ado-net-client-wrong-result-for-coalesce/p1?new=1

How can I pass correctly Time value to the query?

I tried:

[Test]
public async Task TestDirectlyFromConnectionTimeAsTimeSpan()
{
await using var connection = VerticaHelper.OpenLocalVerticaDatabaseConnection();
await using var command = connection.CreateCommand();
command.CommandText = $"SELECT COALESCE(@a, @b) AS \"exp\"";
command.Parameters.Add(new VerticaParameter("a", VerticaType.Time, new TimeSpan(0, 13,53,54, 814255)));
command.Parameters.Add(new VerticaParameter("b", VerticaType.Time, new TimeSpan(0, 13, 51, 54, 814255)));

        DbDataReader reader = await command.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            var value = reader.GetValue(0);
            value.Should().Be(new TimeSpan(0, 13, 53, 54, 814255));
        }
    }

For this case I receive the following exception:
System.IndexOutOfRangeException : Index was outside the bounds of the array.
at Vertica.Data.Internal.ADO.Net.TimeSpanConverter.FromTimeSpan(TimeSpan sourceData, TypeMetadata targetMetadata)
at Vertica.Data.Internal.ADO.Net.SCommand.GetExecutionContextInputs(IList1 targetMetadataList, SParameterCollection paramCollection, IDictionary2 metadataToParam)
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)

    [Test]
    public async Task TestDirectlyFromConnectionTimeAsString()
    {
        await using var connection = VerticaHelper.OpenLocalVerticaDatabaseConnection();
        await using var command = connection.CreateCommand();
        command.CommandText = $"SELECT COALESCE(@a::time(6), @b::time(6)) AS \"exp\"";
        command.Parameters.Add(new VerticaParameter("a", VerticaType.Time, "13:53:54.814255"));
        command.Parameters.Add(new VerticaParameter("b", VerticaType.Time, "13:51:54.814255"));

        DbDataReader reader = await command.ExecuteReaderAsync();
        // GENERATED QUERY: SELECT COALESCE('13:53:54.814255'::time(6), '13:51:54.814255'::time(6)) AS "exp";
        while (await reader.ReadAsync())
        {
            //Returns datetime {01.01.0001 13:53:54}
            var value = reader.GetValue(0);
            value.Should().Be(new TimeSpan(0, 13, 53, 54, 814255));
        }
    }

For this one - the returned value is DateTime, and additionally is trimmed.

kind regards,
Kamil

Jim_Knicely

What's wrong with the second example? That seems to work.

dbadmin=> CREATE TABLE ado AS SELECT COALESCE('13:53:54.814255'::time(6), '13:51:54.814255'::time(6)) AS "exp";
CREATE TABLE

dbadmin=> \x
Expanded display is off.

dbadmin=> \d ado
                                List of Fields by Tables
 Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+------+------+---------+----------+-------------+-------------
 public | ado   | exp    | time |    8 |         | f        | f           |
(1 row)

bysza

Jim, it looks that you're directly connected to Vertica using dbadmin, while Kamil uses Vertica ado.net provider as an intermediary.
Vertica works ok, the problem is with the ado.net provider and its handling of the Time-typed parameter with Coalesce().

Jim_Knicely

Right, but you said that in option number 2, the SQL that ran against the DB was:

SELECT COALESCE('13:53:54.814255'::time(6), '13:51:54.814255'::time(6)) AS "exp";

bysza

Yes, the SQL is correct and result from Vertica is correct. The problem is with Ado.net driver incorrectly interpreting the result and returning the data as trimmed Datetime.

kamil_kliczbor

Dear @Jim_Knicely,
I think it is a general problem of the ADO.NET provider to pass the correct parameter type to the command when we specify VerticaType and equivalent type value. I also found that problem was already identified a few years ago: see https://forum.vertica.com/discussion/234326/vertica-net-driver-does-not-respect-verticaparameter-type.

bysza

I would also add an off-topic that lack of BulkInsert support in Ado.net driver while jdbc driver supports it, looks like a <joke>negative discrimination against the dotnet community/>

Hibiki

Hi @kamil_kliczbor,

Can you accept the following sample code?

[Test]
public async Task TestDirectlyFromConnectionTimeAsTimeSpan()
{
    await using var connection = VerticaHelper.OpenLocalVerticaDatabaseConnection();
    await using var command = connection.CreateCommand();
    command.CommandText = $"SELECT COALESCE(@a, @b) AS \"exp\"";
    command.Parameters.Add(new VerticaParameter("a", VerticaType.VarChar, "0 13:53:54.814255"));
    command.Parameters.Add(new VerticaParameter("b", VerticaType.VarChar, "0 13:51:54.814255"));

    DbDataReader reader = await command.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        var value = reader.GetString(0);
        value.Should().Be("0 13:53:54.814255");
    }
}

Of course, you can change the part of the query to the following:

command.CommandText = "SELECT COALESCE(@a::INTERVAL DAY TO SECOND, @b::INTERVAL DAY TO SECOND) AS \"exp\"";

Hibiki

Hi @bysza,

Does "BulkInsert" mean the batch insert described on the following page?
Using Batch Inserts and Prepared Statements

kamil_kliczbor

@Hibiki said:
Hi @kamil_kliczbor,

Can you accept the following sample code?

[Test]
public async Task TestDirectlyFromConnectionTimeAsTimeSpan()
{
    await using var connection = VerticaHelper.OpenLocalVerticaDatabaseConnection();
    await using var command = connection.CreateCommand();
    command.CommandText = $"SELECT COALESCE(@a, @b) AS \"exp\"";
    command.Parameters.Add(new VerticaParameter("a", VerticaType.VarChar, "0 13:53:54.814255"));
    command.Parameters.Add(new VerticaParameter("b", VerticaType.VarChar, "0 13:51:54.814255"));

    DbDataReader reader = await command.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        var value = reader.GetString(0);
        value.Should().Be("0 13:53:54.814255");
    }
}

Of course, you can change the part of the query to the following:

command.CommandText = "SELECT COALESCE(@a::INTERVAL DAY TO SECOND, @b::INTERVAL DAY TO SECOND) AS \"exp\"";

Hi, @Hibiki
I think it is acceptable.
Can you also can have a look at https://forum.vertica.com/discussion/242318/net-5-ado-net-client-wrong-result-for-coalesce/p1?new=1 ?

thanks,
Kamil

Hibiki

Hi @kamil_kliczbor,

Updated that post as well.

Best regards,

Hibiki

Additional information:

TimeSpan Constructor
TimeSpan(Int32, Int32, Int32, Int32, Int32)
Initializes a new instance of the TimeSpan structure to a specified number of days, hours, minutes, seconds, and milliseconds.

So the last argument should be 3 digits, not 6 digits as it is milliseconds.