Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Open
jpsilvashy opened this issue Feb 9, 2022 · 3 comments

Comments

@jpsilvashy
Copy link

jpsilvashy commented Feb 9, 2022

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
Copy link
Author

jpsilvashy commented Feb 10, 2022

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
Copy link

swistak35 commented May 24, 2023

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:

image

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:

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
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants