6

Using SQL Server 2014.

Given table with rowversion column, other table to join with and a select like this:

 CREATE TABLE dbo.FooTable(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [RowVersion] [rowversion] NULL,
 CONSTRAINT [PK_FooTable] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)) 

 CREATE TABLE dbo.BarTable(
    [Id] [int] IDENTITY(1,1) NOT NULL
 CONSTRAINT [PK_BarTable] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)) 

Insert into BarTable default values
GO


SELECT *
  FROM FooTable ft
  FULL OUTER JOIN BarTable bt on ft.Id = Bt.Id

Result of select is:

Id  RowVersion  Id
NULL    0x      1

Where there was no match with BarTable the RowVersion column is not null, but 0x.

This confuses Dapper, which we use to deserialize result on app server, into thinking, it should construct an object for FooTable part of row - an invalid one, containing just an empty byte array.

Is there any reason for this rather unexpected behaviour?

Are there any smarter solutions than translating the value back to null using CASE in the sql statement or dealing with it in application code?

  • 1
    FWIW, I tested with SQL Server 2017 dbfiddle.uk/… If you are entitled to support, you should open a case and get an explanation. Please post back an explanation if you get one – Lennart Oct 10 at 10:39
  • 4
    SELECT CAST(NULL AS ROWVERSION) would be a simpler repro – Martin Smith Oct 10 at 11:07
  • 4
    Do you have a repro outside of SSMS? I think this is just an issue with how SSMS displays null values of type ROWVERSION. The value of CAST(NULL AS ROWVERSION) is actually Null, not 0x a zero-length binary string. – David Browne - Microsoft Oct 10 at 14:13
  • 2
    The query does return NULL when run from from SQLCMD. – Jacob H Oct 10 at 14:57
  • 2
    @DavidBrowne-Microsoft Yes. The reason I started looking into it in the first place was failing test in C# app, which uses ADO.NET (and Dapper). The resultset on server is deserialized into object which has all properties null except RowVersion which is set empty byte array. With suggestions incorporated into select, object is not created at all because Dapper sees only nulls, which apparently wasn't the case before. So its a driver thing? And SSMS uses it? – Igand Oct 10 at 15:54
4

This appears to be a bug in .NET's SqlClient. The column is being returned as an empty byte[] even though it's marked as null in the result.

EG

using System;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Threading;
using System.Threading.Tasks;
using System.Transactions;

namespace ConsoleApp14
{
    class Program
    {

        static void Main(string[] args)
        {

            using (var con = new SqlConnection("Server=localhost;database=tempdb;Integrated Security=true"))
            {
                con.Open();
                var cmd = con.CreateCommand();
                cmd.CommandText = "select cast(null as rowversion) rv";
                using (var rdr = cmd.ExecuteReader())
                {
                    rdr.Read();
                    var allowDbNull = rdr.GetColumnSchema()[0].AllowDBNull;
                    var isNull = rdr.IsDBNull(0);
                    var val = rdr[0];

                    Console.WriteLine($"SqlClient: AllowDbNull {allowDbNull} IsDbNull: {isNull} {val.GetType().Name} {val}");

                }
            }


            using (var con = new SqlConnection("Server=localhost;database=tempdb;Integrated Security=true"))
            {
                con.Open();
                var cmd = con.CreateCommand();
                cmd.CommandText = "select @val = cast(null as rowversion) ";

                var p = cmd.Parameters.Add(new SqlParameter("@val", System.Data.SqlDbType.Timestamp));
                p.Direction = System.Data.ParameterDirection.Output;

                cmd.ExecuteNonQuery();
                {

                   SqlBinary val = (SqlBinary) p.SqlValue;
                   Console.WriteLine($"SqlClient (parameter): IsDbNull: {val.IsNull} {val.GetType().Name} {val}");

                }
            }

            using (var con = new OdbcConnection("Driver={ODBC Driver 17 for SQL Server};Server=localhost;Trusted_Connection=yes"))
            {
                con.Open();
                var cmd = con.CreateCommand();
                cmd.CommandText = "select cast(null as rowversion) rv";
                using (var rdr = cmd.ExecuteReader())
                {
                    rdr.Read();
                    var allowDbNull = rdr.GetSchemaTable().Rows[0]["AllowDBNull"];
                    var isNull = rdr.IsDBNull(0);
                    var val = rdr[0];

                    Console.WriteLine($"ODBC:      AllowDbNull {allowDbNull} IsDbNull: {isNull} {val.GetType().Name} {val}");

                }

            }


        }
    }
}

Outputs

SqlClient: AllowDbNull True IsDbNull: False Byte[] System.Byte[]
SqlClient (parameter): IsDbNull: True SqlBinary Null
ODBC:      AllowDbNull True IsDbNull: True DBNull

I've opened an issue at https://github.com/dotnet/SqlClient/issues/255 But it's likely to be closed as WontFix. According to notes in the source

// Dev10 Bug #479607 - this should have been the same as SqlDbType.Binary, but it's a rejected breaking change

The issue has been raised, but not fixed as a breaking change. It may get fixed in .NET Core, which is full of breaking changes anyway, and left as-is in .NET Framework.

2

Except the correct and accepted answer being "it's a bug", what you can do about is what ypercube suggests in comment: Cast it to VARBINARY

SELECT ft.[Id], 
       CAST(ft.[RowVersion] AS varbinary(8)),
       bt.[Id] 
FROM FooTable ft 
     FULL OUTER JOIN BarTable bt
     ON ft.Id = Bt.Id ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.