Where not filtering

[akirasankun]

Hi, I am updating the environment and I came across the error in the where clause where the filter is not applied.

environment outdated

Microsoft SQL Server 2016 (SP2-CU4) (KB4464106) - 13.0.5233.0 (X64) CentOS Linux release 7.7.1908 (Core) Tds_fdw | 1.0.2 PostgreSQL 9.3.5

EXPLAIN SELECT *FROM ftb_cidades where cod_cidade = '1';
NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'master'., NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 1, Msg: Changed database context to 
NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'master'., NOTICE:  DB-Library notice: Msg #: 5701, Msg state: 1, Msg: Changed database context to 

QUERY PLAN
 Foreign Scan on ftb_cidades  (cost=25.00..5589.00 rows=5564 width=522)
   Filter: ((cod_cidade)::text = '1'::text)
(2 rows)

environment updated

Microsoft SQL Server 2016 (SP2-CU4) (KB4464106) - 13.0.5233.0 (X64) CentOS Linux release 7.7.1908 (Core) Tds_fdw | 2.0.1 PostgreSQL 12.2

EXPLAIN SELECT *FROM ftb_cidades where cod_cidade = '1';
NOTICE:  tds_fdw: Query executed correctly
NOTICE:  tds_fdw: Getting results
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Foreign Scan on ftb_cidades  (cost=200.00..556555.64 rows=5564 width=100)
(1 row)

In the outdated environment the filter works correctly and in the updated no works in all foreign tables

[juliogonzalez]

I am not saying I can help but: can you share the table definitions both on MSSQL and PostgreSQL, as well as the encodings on both sides?

[akirasankun]

PostgreSQL en_us.utf-8 MSSQL Server collation SQL_Latin1_General_CP1_CI_AS Column Collation SQL_Latin1_General_CP850_CI_AS

Both environments connect to the same MSSQL

[juliogonzalez]

Is the FreeTDS config the same at both environments?

[akirasankun]

Those are the differences

Postgres 9.3

[global]
        # TDS protocol version
;       tds version = 4.2

[egServer70]
        host = ntmachine.domain.com
        port = 1433
        tds version = 7.0

Postgres 12

[global]
        # TDS protocol version
        tds version = auto
[egServer73]
        host = ntmachine.domain.com
        port = 1433
        tds version = 7.3

[juliogonzalez]

Can you adjust the config at the PostgreSQL 12 to be the same as at 9.3? (you don't need to change [egServer73] to be [egServer70]

A I am not sure if such change will require a PostgreSQL reload.

The way I see it, there could be three suspects here: 1) Problems caused by tds_version (unlikely) 2) Changes on the PostgreSQL fdw side (FMPOV unlikely as well, but who knows, you are changing from 9.3 to 12, and that's a long way) 3) Changes on tds_fdw since 1.0.2 (that version is almost 5 years old).

But before someone starts having a look at tds_fdw (I can try, but I am not the developer, and neither C developer or familiar with PostgreSQL internals)... let's make sure both environments are "almost" the same.

I'd also suggest another test, if you can do it: try 2.0.1 with PostgreSQL 9.3 and see if you can reproduce the issue.

Despite we don't test 9.3 at the CI anymore, it should still build.

[akirasankun]

Thanks for help, the a change not soluction the problemen.

PostgreSQL 9.3 is a production server that cannot be changed without first being consolidated for approval

[juliogonzalez]

I was more thing about provisioning a new 9.3 sever which is a clone of the first one, but with tds_fdw 2.0.1.

If not a clone, at least a new 9.3 server which is as similar as possible as the one where things are working, but with tds_fdw 2.0.1

[ztr-syndeste]

I am also experiencing issues with WHERE not filtering from the foreign tables. In my case the example is field with one of two possible text values. Interestingly, when I select distinct on that field with a count, it returns 33 rows, each with one of the two distinct values, and each with some count of a chunk of records. If I select the foreign table into a new local table, WHERE and select distinct behave as expected though. Wondering if that could be a clue here.

[ztr-syndeste]

Likewise, ORDER BY doesn't seem to function as intended.

[Jnb2387]

Not using the query option seem to correct this for me. https://github.com/tds-fdw/tds_fdw/issues/195#issuecomment-504452867

[cooltnt]

Hi @GeoffMontee

I had the same problem.

tds_fdw 2.0.2 & 2.0.3 postgresql 11.11 & 14

CREATE FOREIGN TABLE public.test ( userid text NULL ) SERVER mssql_local OPTIONS (query 'SELECT userid FROM dbo.test');

select count(*) from public.test ; -- 374830 records

select count(*) from public.test where userid = '268' -- 374830, unexpected, should be 1000 ;

If is not use CTE for type casting, are there any other methods ?

[cuervotux]

Same issue here, postgresql 9.4 tds_fdw 1.0.7 works fine postgresql 15 tds_fdw 2.0.3 not filtering when the foreign table is defined by query, if the foreign table is defined by schema_name/table_name.

The workaround I found is to cast datatypes in the where clause ie: date::varchar(10) = '2023-07-01' with some castings works.