Incorrect work of JOIN clause

[rentechpro]

create foreign table ms_tables ( tab_name varchar(128), col_name varchar(128), typ_name varchar(128), len smallint ) server fs_rc options (query ' select so.name as tab_name, sc.name as col_name, st.name as typ_name, sc.length as len from sysobjects so join syscolumns sc on sc.id = so.id join systypes st on st.xtype = sc.xtype where so.xtype = ''U'' order by so.name, sc.colid; ');

select count (1) from ms_tables; -- 109 rows

select count (1) -- 16 rows from information_schema.tables t where table_schema = 'dbo' and table_type = 'BASE TABLE';

select count (1) -- 12 rows from information_schema.tables t join ms_tables m on lower (m.tab_name) = lower (t.table_name) where table_schema = 'dbo' and table_type = 'BASE TABLE';

select count (1) -- 103 rows from information_schema.tables t full join ms_tables m on lower (m.tab_name) = lower (t.table_name) where table_schema = 'dbo' and table_type = 'BASE TABLE';

The schema dbo has 16 same table names on both sides. but join see 1 the same table name only!

The full join sees all 16 table names but simple join see the same quantity.

Postgres 9.6.1, tds_fdw-1.0.8, MS SQL 2008R2.

[Smtgr14]

Can you clarify this issue a bit more? I'm not getting what the issue is exactly. What result are you expecting? What happens instead? What steps are you taking that cause this to appear?

[rentechpro]

There are some tables on MS SQL which are used as foreign tables in PG SQL. Also there are some usual tables with the same structure in PG SQL. All queries listed above have been executed on Postgres 9.6.1 with the help of PGAdmin 4.1.1. The query select count (1) -- 12 rows from information_schema.tables t join ms_tables m on lower (m.tab_name) = lower (t.table_name) where table_schema = 'dbo' and table_type = 'BASE TABLE'; expects the result of 103 rows (each table_name in schema with complete list of column names).

For example:

MS_TABLES table_name1, col_name11, int, null table_name1, col_name12, int, null table_name2, col_name21, int, null table_name2, col_name22, int, null table_name2, col_name23, int, null

information_schema.tables (table_name only) table_name1 table_name2

The query above will show 2 records counting only records below - table_name1, col_name11, int, null table_name1, col_name12, int, null

Why?? Where are other 3 records with table_name2?

[Smtgr14]

Does this query work properly on the MSSQL side? You may also want to try building each table as a foreign table and allow PostgreSQL to try doing the joins. You can also use this to verify all the rows from each table on the MSSQL side are coming over properly. Also ensure there is no row security on MSSQL's side that is actively preventing the viewing of certain rows.

[rentechpro]

1. This is a specific PG query from specific PG system and foreign tables. So it can be executed on the PG SQL Server only. So the question "Does this query work properly on the MSSQL side" does not have any mean.
2. "You may also want to try building each table as a foreign table and allow PostgreSQL to try doing the joins." I think it have no mean also because of correct work of foreign table ms_tables. Why it ought to break the correct foreign table?
3. I wrote that foreign table ms_tables works correctly. The error is in the other place.
4. It's everything OK with security on MSSQL side. The error is in the result of the join. Can you reproduce this error?