tds_fdw
tds_fdw copied to clipboard
tds-fdw
Local subquery being corrupted - query producing incorrect results
Local DB: Postgres 10.4
postgres=# select version();
version
--------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 10.4 (Ubuntu 10.4-2.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit
(1 row)
postgres=#
Remote DB: SQL Server 2008 R2
tds_fdw built from master
FreeTDS: http://www.freetds.org/files/current/freetds-dev.1.00.384.tar.gz
Minimal test case follows.
Local table:
create table t2 ( id int not null ) ;
insert into t2 values ( 1 ) ;
Remote (SQL Server) table creation:
create table t1 ( id int not null ) ;
insert into t1 values ( 1 ) ;
insert into t1 values ( 2 ) ;
insert into t1 values ( 3 ) ;
Foreign table created in Postgres:
create foreign table t1
(
id integer not null
)
SERVER mssql_svr
OPTIONS
(
schema_name 'dbo',
table 't1'
);
Data:
postgres=# select id from t1;
id
----
1
2
3
(3 rows)
postgres=# select max(id) from t2 ;
max
-----
1
(1 row)
postgres=#
Erroring query:
postgres=# select t1.id from t1 where t1.id > ( select max(id) from t2 ) ;
id
----
(0 rows)
postgres=#
The above query should return 2 & 3, but returns nothing. The following logically equivalent query actually works:
postgres=# select t1.id from t1 where t1.id > ( 1 ) ;
id
----
2
3
(2 rows)
postgres=#
Log snippet of transformed query: DEBUG: tds_fdw: Setting database command to SELECT [id] FROM [dbo].[t1] WHERE (([id] > (CAST((SELECT CAST(null as integer)) as integer)))) - It looks like the local part is being destroyed somehow by being nulled.
DEBUG output:
postgres=# select t1.id from t1 where t1.id > ( select max(id) from t2 ) ;
DEBUG: tds_fdw: checking if an expression is safe to execute remotely
DEBUG: tds_fdw: it is an op or distinct expression
DEBUG: tds_fdw: checking if an expression is safe to execute remotely
DEBUG: tds_fdw: it is a list expression
DEBUG: tds_fdw: checking if an expression is safe to execute remotely
DEBUG: tds_fdw: it is a var expression
DEBUG: tds_fdw: checking if an expression is safe to execute remotely
DEBUG: tds_fdw: it is a param expression
DEBUG: tds_fdw: Using remote estimate
DEBUG: tds_fdw: checking if an expression is safe to execute remotely
DEBUG: tds_fdw: it is an op or distinct expression
DEBUG: tds_fdw: checking if an expression is safe to execute remotely
DEBUG: tds_fdw: it is a list expression
DEBUG: tds_fdw: checking if an expression is safe to execute remotely
DEBUG: tds_fdw: it is a var expression
DEBUG: tds_fdw: checking if an expression is safe to execute remotely
DEBUG: tds_fdw: it is a param expression
DEBUG: tds_fdw: Getting query
DEBUG: tds_fdw: deparsing an expression
DEBUG: tds_fdw: deparsing an operator expression
DEBUG: tds_fdw: deparsing an expression
DEBUG: tds_fdw: deparsing a var
DEBUG: tds_fdw: deparsing an expression
DEBUG: tds_fdw: deparsing a param
DEBUG: tds_fdw: deparsing an expression
DEBUG: tds_fdw: deparsing an operator expression
DEBUG: tds_fdw: deparsing an expression
DEBUG: tds_fdw: deparsing a var
DEBUG: tds_fdw: deparsing an expression
DEBUG: tds_fdw: deparsing a param
DEBUG: tds_fdw: Value of query is SELECT [id] FROM [dbo].[t1] WHERE (([id] > (CAST((SELECT CAST(null as integer)) as integer)))) AND (([id] > (CAST((SELECT CAST(null as integer)) as integer))))
DEBUG: tds_fdw: Initiating DB-Library
DEBUG: tds_fdw: Getting login structure
DEBUG: tds_fdw: Setting login user to sa
DEBUG: tds_fdw: Setting login password to **********
DEBUG: tds_fdw: Setting login database to ************
DEBUG: tds_fdw: Connection string is ***:49898
DEBUG: tds_fdw: Connecting to server
NOTICE: DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to '************'., Server: ********-******\***, Process: , Line: 1, Level: 0
NOTICE: DB-Library notice: Msg #: 5703, Msg state: 1, Msg: Changed language setting to us_english., Server: ********-******\***, Process: , Line: 1, Level: 0
DEBUG: tds_fdw: Connected successfully
DEBUG: tds_fdw: Setting database command to SET SHOWPLAN_ALL ON
DEBUG: tds_fdw: Executing the query
DEBUG: tds_fdw: Query executed correctly
DEBUG: tds_fdw: Getting results
DEBUG: tds_fdw: Setting database command to SELECT [id] FROM [dbo].[t1] WHERE (([id] > (CAST((SELECT CAST(null as integer)) as integer)))) AND (([id] > (CAST((SELECT CAST(null as integer)) as integer))))
DEBUG: tds_fdw: Executing the query
DEBUG: tds_fdw: Query executed correctly
DEBUG: tds_fdw: Getting results
DEBUG: tds_fdw: 18 columns
DEBUG: tds_fdw: Binding column Parent (4)
DEBUG: tds_fdw: Binding column EstimateRows (9)
DEBUG: tds_fdw: Successfully got results
DEBUG: tds_fdw: Parent is 0. EstimateRows is 0.
DEBUG: tds_fdw: Parent is 1. EstimateRows is 0.
DEBUG: tds_fdw: We estimated 0 rows.
DEBUG: tds_fdw: Setting database command to SET SHOWPLAN_ALL OFF
DEBUG: tds_fdw: Executing the query
DEBUG: tds_fdw: Query executed correctly
DEBUG: tds_fdw: Getting results
DEBUG: tds_fdw: Estimated rows = 1.000000, estimated width = 100
DEBUG: tds_fdw: Getting query
DEBUG: tds_fdw: deparsing an expression
DEBUG: tds_fdw: deparsing an operator expression
DEBUG: tds_fdw: deparsing an expression
DEBUG: tds_fdw: deparsing a var
DEBUG: tds_fdw: deparsing an expression
DEBUG: tds_fdw: deparsing a param
DEBUG: tds_fdw: Value of query is SELECT [id] FROM [dbo].[t1] WHERE (([id] > (CAST((SELECT CAST(null as integer)) as integer))))
DEBUG: tds_fdw: Initiating DB-Library
DEBUG: tds_fdw: Getting login structure
DEBUG: tds_fdw: Setting login user to sa
DEBUG: tds_fdw: Setting login password to *************
DEBUG: tds_fdw: Setting login database to ************
DEBUG: tds_fdw: Connection string is ***:49898
DEBUG: tds_fdw: Connecting to server
NOTICE: DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to '********'., Server: ********-******\***, Process: , Line: 1, Level: 0
NOTICE: DB-Library notice: Msg #: 5703, Msg state: 1, Msg: Changed language setting to us_english., Server: ********-******\***, Process: , Line: 1, Level: 0
DEBUG: tds_fdw: Connected successfully
DEBUG: tds_fdw: This is the first iteration
DEBUG: tds_fdw: Setting database command to SELECT [id] FROM [dbo].[t1] WHERE (([id] > (CAST((SELECT CAST(null as integer)) as integer))))
DEBUG: tds_fdw: Executing the query
DEBUG: tds_fdw: Query executed correctly
DEBUG: tds_fdw: Getting results
DEBUG: tds_fdw: Successfully got results
DEBUG: tds_fdw: Getting column info
DEBUG: tds_fdw: 1 columns
DEBUG: tds_fdw: Table OID is 24656
DEBUG: tds_fdw: Fetching column 0 (id)
DEBUG: tds_fdw: Type is 56
DEBUG: tds_fdw: Matching foreign column with local column by name.
DEBUG: tds_fdw: Comparing it to the following retrived column: 0
DEBUG: tds_fdw: Comparing retrieved column name to the following local column name: id
DEBUG: tds_fdw: It matches!
DEBUG: tds_fdw: Local index = 0, local type OID = 23
DEBUG: tds_fdw: The foreign type is 56. The local type is 23.
DEBUG: tds_fdw: Fetching next row
DEBUG: tds_fdw: No more rows
DEBUG: tds_fdw: Closing database connection
DEBUG: tds_fdw: Freeing login structure
DEBUG: tds_fdw: Closing DB-Library
id
----
(0 rows)
postgres=#
I've also reproduced using names that don't clash & the result is the same
Casting the remote part of the query to bigint works.
select t1.id from t1 where t1.id::bigint > ( select max(id) from t2 ) ;
... but this disables query pushdown, which it the entire reason I looked at tds_fdw in the first place.