Problem with FreeTDS text comparisons

[jgoizueta]

This is specific to the FreeTDS odbc driver.

If we have a condition on a foreign table, comparing a text column to a text literal,
the condition (qual) will be transferred to the ODBC query and it will fail because FreeTDS does not allow comparing a text to a varchar (which is the type of the literal on the remote side).

A workaround is to cast the text column to varchar (casting the literal to text has no effect on the query executed remotely) because it removes the qual from the remote query.

Example: remote SQL Server database testdb:

create table txt(t text);

With this foreign table:

 CREATE SERVER ms FOREIGN DATA WRAPPER odbc_fdw
   OPTIONS (
     odbc_DRIVER 'FreeTDS',
     "odbc_Server" '...',
     "odbc_Database" 'testdb'
   );
 CREATE USER MAPPING FOR postgres SERVER ms
   OPTIONS (
     "odbc_UID" 'dbo',
     "odbc_PWD" '...'
   );
IMPORT FOREIGN SCHEMA dbo
  FROM SERVER ms
  INTO public
  OPTIONS(
    table 'txt',
    ApplicationIntent 'ReadOnly'
  );

Now this will fail:

SELECT * from txt where t = '';

With this error:

[FreeTDS][SQL Server]The data types text and varchar are incompatible in the equal to operator.

Workaround:

SELECT * from txt where t::varchar = '';