Result from tiny_tds does not match result from freetds or sql server

[noel]

On SQL Server this:

select ISNULL(CAST(NULL AS varchar(10)) + '3', '34') as c

evaluates to:
34

The same is true using from freetds via tsql

1> select ISNULL(CAST(NULL AS varchar(10)) + '3', '34') as c
2> go
c
34

but not via tiny_tds

irb(main):019:0> r= client.execute("select ISNULL(CAST(NULL AS varchar(10)) + '3', '34') as c")
=> #<TinyTds::Result:0x9dd026c>
irb(main):020:0> r.each
=> [{"c"=>"3"}]

[metaskills]

Can you break down what the is happening above and what is supposed to be returned by each expression?

[noel]

I'm sorry, I'm not sure if I understand the question.

In all cases the result should be 34, but when going through tiny_tds the result is 3 because the select statement below is not evaluating to NULL I believe.

If you do this in SQL Server the result is NULL

select CAST(NULL AS varchar(10)) + '3' as c

So we expect the ISNULL to function to evaluate to 34

[metaskills]

Odd, I would expect the proper return value for that expression to be '3' not null. Perhaps you are experience a difference in ANSI settings which may also be affected by your FreeTDS version and TDS version set. Can you report to me what FreeTDS version you are using and when you connect, what does TinyTds::Client#tds_version return when connected.

Also, have you seen what the ActiveRecord adapter does when using TinyTDS once it connects? Specifically the ANSI settings we use.

https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/blob/master/lib/active_record/connection_adapters/sqlserver_adapter.rb#L437-L439

Once you get me that info back on your versions, can you examine that and under what ANSI settings (if any) your expression would return different results.

[noel]

I'll look into the question you raise above, but wanted to show you the query directly in SQL Server Management Studio. You are expecting NULL to be CAST to an empty string?

Have a look at this. The first answer has this:
CAST of a NULL returns NULL and NULL + something is also NULL
http://stackoverflow.com/questions/7557588/how-to-control-casting-of-null-int-field-to-varchar-in-sql-server

[noel]

irb(main):006:0> client.tds_version
=> 9

Regarding the comment about ActiveRecord, I am not using AR in these tests. Simply irb and tiny_tds.

[metaskills]

Regarding the comment about ActiveRecord...

Which makes the point even more poignant. By default DBLIB connections are not exactly like what you would expect default SMS connections. So can you please QA those ANSI settings. They are all over the MDN site.

[metaskills]

FWIW, I would start off using the setting the adapter does, then do more investigation as needed.

[noel]

From a Rails app I tried find_by_sql and also got 3

back in IRB I tried doing this before client execute. Still got 3.

client.execute("SET ANSI_DEFAULTS ON").do
client.execute("SET CURSOR_CLOSE_ON_COMMIT OFF").do
client.execute("SET IMPLICIT_TRANSACTIONS OFF").do

Is this what you wanted me to try? What is the MDN? You mean the Microsoft site?

I'll look around more, but interesting that even going through ActiveRecord I get the same result

[noel]

Wrong button by mistake.

In a Rails app I went through ActiveRecord and also got the same result.

I will look around more on the Microsoft site, but the fact that it works via freeTDS I think tells us it is something being done when we get to tiny_tds

[metaskills]

Is this what you wanted me to try?

Yes.

What is the MDN? You mean the Microsoft site?

Yes, the Microsoft Developer Network.

I'll look around more, but interesting that even going through ActiveRecord I get the same result

Yes, helpful, but do not stop there. For instance, this ticket here was resolved by a users setting CONCAT_NULL_YIELDS_NULL. The links for it are below as well as other general settings. These are what I would like you to investigate to see if there is a setting. Because basically, I already know that TinyTDS is not doing anything to block you from this, I firmly believe it is a setting and it may even be one that makes logical sense to add to the adapter to make sure DBLIB/TinyTDS acts more as you would expect.

http://msdn.microsoft.com/en-us/library/ms176056.aspx
http://msdn.microsoft.com/en-us/library/ms190356.aspx

[sixfeetover]

@metaskills is correct here, what you are looking for is set concat_null_yields_null on. As the name implies, this determines whether concatenating NULL to a string results in NULL (on), or if NULL was treated like an empty string (off). This is set at the connection level. SSMS has this on by default; many other connections (like FreeTDS) do not. When I want this on in my Rails apps, I create an initializer that looks like this:

module ActiveRecord
  module ConnectionAdapters
    class SQLServerAdapter < AbstractAdapter
      def configure_connection
        raw_connection_do "set concat_null_yields_null on"
      end
    end
  end  
end

[noel]

@sixfeetover that was it. Now it works as I was expecting.

So then the question is why have the default behavior differ from that of SQL Server Management Studio?

[metaskills]

We should not. Can someone open an issue on the adapter page and link it to this one? I will make sure to test that setting in the next release.