SQL Server function error

1

I have a SQL Server 2008 database set up as a Dremio source. When I try and run a SELECT that includes a CONCAT function call

SELECT A, CONCAT('pre-', "B") as PreB FROM TABLE

I get the following error:

com.microsoft.sqlserver.jdbc.SQLServerException: 'CONCAT' is not a recognized built-in function name.

CONCAT was not introduced to SQL server until 2012 but I had assumed Dremio would fallback on its own SQL engine?

2008 does allow string concatenation using + but if I try that i get a calcite error:

org.apache.calcite.sql.validate.SqlValidatorException: Cannot apply '+' to arguments of type '<VARCHAR(4)>....'

Is there anyway to work around this?

2

I have just found a workaround, for SQL server 2008 you can use

SELECT A, {fn CONCAT('pre-', B)} as PreB FROM TABLE

This calls the ODBC function in SQL Server and calcite parses it.

However it still feels like a leaky abstraction to me and I would like to know what the correct solution would be.

3

@jharrington

Dremio only supports SQL Server 2012 and above, which we are missing in our documentation (opened a ticket to fix that). So for your case there probably is no better way.

4

Ok, disappointed to hear that. Thanks for the response.