MSSQL Connector Incorrectly Plans/Casts Boolean Operations

1

If a MS SQL Table contains a bit type column, and you try to query it using column_name = 1 then the query planner translates this to, WHERE [column_name]. This is invalid syntax for MS SQL, so it throws the error:

An expression of non-boolean type specified in a context where a condition is expected

If I try use Dremi’s internal functions to cast it to integer, CAST(column_name AS int) = 1 I get the error:

Cast function cannot convert value of type BOOLEAN to type INTEGER

I found that as a workaround I can cast it to a string and get a valid comparison, CAST(column_name AS varchar(1)) = '1'.

2

@patricker

Dremio treats SQL server datatype “bit type” as BOOLEAN and hence this behavior

SQL Server Bit Type

3

@balaji.ramaswamy I’m glad that the behavior is understood. Is this expected behavior? Is there a way of adjusting the ARP Adapter to make this logic easier to work with? Right now it’s not very intuitive.