GetFieldValueAsync<Stream> after IsDBNullAsync returns an empty stream

[bbowyersmyth]

Describe the bug

With SequentialAccess, calling GetFieldValueAsync<Stream> on its own returns a readable stream. If IsDBNullAsync is called first then the stream will have no data.

Using GetStream instead returns a readable stream in both cases.

To reproduce

Based on GetFieldValueAsync_OfStream unit test

using (SqlDataReader reader = await command.ExecuteReaderAsync(CommandBehavior.SequentialAccess))
{
    if (await reader.ReadAsync() && !await reader.IsDBNullAsync(1))
    {
        using var buffer = new MemoryStream(originalData.Length);

        // Doesn't have any bytes to read
        using (Stream stream = await reader.GetFieldValueAsync<Stream>(1))
        {
            await stream.CopyToAsync(buffer);
            outputData = buffer.ToArray();
        }

        // Works
        //using (Stream stream = reader.GetStream(1))
        //{
        //    await stream.CopyToAsync(buffer);
        //    outputData = buffer.ToArray();
        //}
    }
}

Debug.Assert(originalData.SequenceEqual(outputData));

Expected behavior

Expect GetFieldValueAsync<Stream> to work the same as GetStream in that IsDBNullAsync doesn't consume the column

Further technical details

Microsoft.Data.SqlClient version: 5.2.2
.NET target: .NET 8
SQL Server version: 2022
Operating system: Windows 11

[bbowyersmyth]

Was this by design at all @Wraith2 ?

[Wraith2]

Apologies for the late reply.

This may be intentional but it's impossible to tell unless we find the person who wrote it. The sync apis like GetStream and the task api GetFieldValueAsync<T> are from different development epochs in the library. I've suggested in the past that the original GetStream type api was made so that it always returned a valid stream even if that stream represented a null so that programmers could shortcut the null check and still not get exceptions.
That idea may not have been part of the design for the later GetfieldValueAsync function.

It is my opinion that calling GetFieldValueAsync<Stream> when the db value is null should return the same result regardless of whether IsDBNull/IsDBNullAsync has been called first.
It seems reasonable to expect that calling GetFieldValueAsync should return the same empty but non-null stream that calling GetStream would return.
I don't know how hard it would be to change this and how likely to would be to break someone who relies on this weird behaviour.

[roji]

It is my opinion that calling GetFieldValueAsync when the db value is null should return the same result regardless of whether IsDBNull/IsDBNullAsync has been called first.

See npgsql/npgsql#5934 for a discussion on this in Npgsql. FWIW I'm not sure why Stream/TextReader should be made an exception to the general ADO.NET behavior of throwing when the database value is NULL - GetString() doesn't return an empty string for null so I'm not sure why GetTextReader would return a TextReader over an empty string etc.