Predicate on Sybase ASE binary column failes

select from foreign table fails if there is predicate on column data type ASE - binary, foreign table BYTEA.

=# select branch_id from ase.tds_tbl where branch_id = E'\\x00038500875c3d60'::bytea;
NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'customer'.
, Server: FMI0MA1, Process: , Line: 0, Level: 0
NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 1, Msg: Changed database context to 'vendor'.
, Server: FMI0MA1, Process: , Line: 1, Level: 0
NOTICE:  DB-Library notice: Msg #: 102, Msg state: 181, Msg: Incorrect syntax near 'E'.
, Server: FMI0MA1, Process: , Line: 1, Level: 15
ERROR:  DB-Library error: DB #: 102, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 15
Time: 0.209 ms

Same test different syntax:

=# select branch_id from ase.tds_tbl where branch_id = decode('00038500875c3d60', 'hex');
NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'customer'.
, Server: FMI0MA1, Process: , Line: 0, Level: 0
NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 1, Msg: Changed database context to 'vendor'.
, Server: FMI0MA1, Process: , Line: 1, Level: 0
NOTICE:  DB-Library notice: Msg #: 102, Msg state: 181, Msg: Incorrect syntax near 'E'.
, Server: FMI0MA1, Process: , Line: 1, Level: 15
ERROR:  DB-Library error: DB #: 102, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 15
Time: 0.209 ms

when casted to text (suitable for small tables only)

=# select branch_id from ase.tds_tbl where branch_id::text = '\x00038500875c3d60';
┌────────────────────┐
│     branch_id      │
├────────────────────┤
│ \x00038500875c3d60 │
└────────────────────┘
(1 row)

Another approach:

[local]:5432 postgres@postgres:13550
=# select branch_id from ase.tds_tbl where branch_id = (select branch_id from ase.tds_tbl where branch_id::text = '\x00038500875c3d60');
NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'customer'.
, Server: FMI0MA1, Process: , Line: 0, Level: 0
NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 1, Msg: Changed database context to 'vendor'.
, Server: FMI0MA1, Process: , Line: 1, Level: 0
NOTICE:  tds_fdw: Query executed correctly
NOTICE:  tds_fdw: Getting results
NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'customer'.
, Server: FMI0MA1, Process: , Line: 0, Level: 0
NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 1, Msg: Changed database context to 'vendor'.
, Server: FMI0MA1, Process: , Line: 1, Level: 0
NOTICE:  DB-Library notice: Msg #: 2715, Msg state: 1, Msg: Can't find type 'bytea'.
, Server: FMI0MA1, Process: , Line: 1, Level: 16
ERROR:  DB-Library error: DB #: 2715, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 16
Time: 0.249 ms

testcase information:

[local]:5432 postgres@postgres:13713
=# \des+ ase
                                                                                      List of foreign servers
┌───────────┬──────────┬──────────────────────┬───────────────────┬──────┬─────────┬────────────────────────────────────────────────────────────────────────────────────────────────────────┬─────────────┐
│   Name    │  Owner   │ Foreign-data wrapper │ Access privileges │ Type │ Version │                                           FDW options                                                  │ Description │
├───────────┼──────────┼──────────────────────┼───────────────────┼──────┼─────────┼────────────────────────────────────────────────────────────────────────────────────────────────────────┼─────────────┤
│ ase       │ postgres │ tds_fdw              │                   │      │         │ (servername '<IP ADDR>', port '<PORT NO>', database 'vendor', tds_version '5.0', msg_handler 'notice') │             │
└───────────┴──────────┴──────────────────────┴───────────────────┴──────┴─────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────┴─────────────┘
(1 row)

Table definition:

=# \d ase.tds_tbl
                               Foreign table "ase.tds_tbl"
┌─────────────────────┬────────────────────────┬───────────┬──────────┬─────────┬─────────────┐
│       Column        │          Type          │ Collation │ Nullable │ Default │ FDW options │
├─────────────────────┼────────────────────────┼───────────┼──────────┼─────────┼─────────────┤
│ branch_id           │ bytea                  │           │ not null │         │             │
│ city                │ character varying(60)  │           │          │         │             │
│ zip_code            │ character varying(10)  │           │          │         │             │
└─────────────────────┴────────────────────────┴───────────┴──────────┴─────────┴─────────────┘
Server: ase
FDW options: (schema_name 'dbo', table_name 'branch')

use vendor
go

sp_columns branch
go

table_qualifier |table_owner |table_name |column_name         |data_type |type_name |precision |length |scale |radix |nullable |remarks |ss_data_type |colid |column_def |sql_data_type |sql_datetime_sub |char_octet_length |ordinal_position |is_nullable |
----------------|------------|-----------|--------------------|----------|----------|----------|-------|------|------|---------|--------|-------------|------|-----------|--------------|-----------------|------------------|-----------------|------------|
vednor          |dbo         |branch     |branch_id           |-2        |binary    |8         |8      |      |      |0        |        |45           |1     |           |-2            |                 |8                 |1                |NO          |
vednor          |dbo         |branch     |city                |12        |varchar   |60        |60     |      |      |1        |        |39           |3     |           |12            |                 |60                |3                |YES         |
vednor          |dbo         |branch     |zip_code            |12        |varchar   |10        |10     |      |      |1        |        |39           |9     |           |12            |                 |10                |9                |YES         |

TDS information: tds_fdw compiled from git: * master 210299b Merge pull request #173 from laurenz/standby

RDBMS:

postgres=# select version();
                                                              version
-----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.5 (Ubuntu 10.5-1.pgdg14.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.4) 4.8.4, 64-bit
(1 row)

postgres=# \dx
                                               List of installed extensions
  Name   |    Version    |   Schema   |                                    Description
---------+---------------+------------+-----------------------------------------------------------------------------------
 plpgsql | 1.0           | pg_catalog | PL/pgSQL procedural language
 tds_fdw | 2.0.0-alpha.2 | public     | Foreign data wrapper for querying a TDS database (Sybase or Microsoft SQL Server)
(2 rows)

Freetds:

$ apt list --installed | grep tds

WARNING: apt does not have a stable CLI interface yet. Use with caution in scripts.

freetds-bin/trusty,now 0.91-5 amd64 [installed]
freetds-common/trusty,now 0.91-5 all [installed]
freetds-dev/trusty,now 0.91-5 amd64 [installed]
tdsodbc/trusty,now 0.91-5 amd64 [installed]

tds-fdw / tds_fdw

Predicate on Sybase ASE binary column failes #180