ActiveRecord::StatementInvalid (TinyTds::Error: Type DT_BooleanFalse is not a defined system type.)

[jpsilvashy]

Issue

I'm trying to query a DB which has some custom types, I've not seen before, one column has the type TD_BooleanFalse, when querying the db, the following error is raised:

ActiveRecord::StatementInvalid (TinyTds::Error: Type DT_BooleanFalse is not a defined system type.)

I've tried registering a type with an initializer like so:

ActiveRecord::Type.register(:DT_BooleanFalse, ActiveRecord::Type::SQLServer::Boolean)

And then setting the attribute type in the model with:

attribute :isDeleted, :DT_BooleanFalse

Although this does not work.

Details

• Rails version: 6.1.4.4

• SQL Server adapter version: v6.1.2.1

• TinyTDS version: v1.3.7

• FreeTDS details:

                            Version: freetds v1.3.7
             freetds.conf directory: /opt/homebrew/etc
     MS db-lib source compatibility: no
        Sybase binary compatibility: yes
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 7.3
                              iODBC: no
                           unixodbc: yes
              SSPI "trusted" logins: no
                           Kerberos: yes
                            OpenSSL: yes
                             GnuTLS: no
                               MARS: yes
  

[jpsilvashy]

Actually after further investigation, I found out something interesting, this only happens for default value constraints for non-standard column types.

So this is still an issue, but we've worked around it by having the client drop the constraints, but leaving my original issue here in case anyone comes across it via search or otherwise.

[swistak35]

Hey,
I hit the same issue and partly figured out -- I'm far from SQL Server expert and this is only one of plenty databases I have to deal with, so not much time to dig deeper. But I should be easily be able to test out possible solutions quickly, if necessary.

The issue is that in this optimization commit we have changed the source of truth for the columns information, i.e. we now use different tables, and if we use custom types in our tables, the "type" being fetched is different than previously.

For example, assuming following custom type:

In old version, we were fetching the "system type" as a type (for example, int). In new version, we fetch "custom type" (for example, DM_BOOL).

Then, in the code around default values, we have this line:

activerecord-sqlserver-adapter/lib/active_record/connection_adapters/sqlserver/schema_statements.rb

Line 430 in 28c38ea

value = select_value("SELECT CAST(#{value} AS #{type}) AS value", "SCHEMA")

In our example, it tries to execute following SQL:

SELECT CAST(0 AS DM_BOOL) AS value

Because now, the type is DM_BOOL, not int. And here it fails, with error:

TinyTds::Error: Type DM_BOOL is not a defined system type.

Because, as far as I understand -- we cannot cast the value into a custom type, only into system type. In previous version it tries to cast into int (and it works, of course).

My guess would be that the easiest solution would be to change the code so that it fetch system type instead of custom type again, but I don't know whether it's possible to do that and keep the optimizations in place. Or maybe fetch both the custom type and system type, and then use system type for casting. I honestly don't know what's best, my knowledge in SQL Server is really basic.

---

When it comes for workaround, I have recovered myself the unoptimized version of column definitions (monkeypatched column_definitions_sql method) and it works for me well. If you would have some different solutions you would like to try, I could try them out in my project, since obviously I would prefer to have as little monkeypatches as possible :)

Also for the record:

ActiveRecord version: 6.1.7.3
SQL Server adapter version: 6.1.2.1

But in my understanding, the problem should also be the same in 7.x version.

[swistak35]

Also, the issue is closed and I don't have ability to reopen it :)