Unable to read tables from MSSQL2012 database when the foreign tables are created with schema_name/table_name parameters

[naegleria]

Hello,

I've installed the github version of tds_fdw on Ubuntu 14.04 and Postgresql 9.4 and am trying to read tables from a MSSQL 2012 11.0.5058 SP2 database using tds_version 7.1. It seems to connect to the database just fine, but then a 3rd notice message appears: NOTICE: DB-Library notice: Msg #: 4145, Msg state: 1, Msg: an expression of non-boolean type specified in a context where a condition is expected, near 'AND'., Server: server\instance, Process: , Line 1, Level: 15 And after that comes the generic "General SQL Server error".

I've got 10 foreign tables defined and all of them give me that same message, here's an example of one of the tables create query: CREATE FOREIGN TABLE schema.table (attributeid character varying(32) , name character varying(64) , typeid character varying(32) ) SERVER foreign_server OPTIONS (schema_name 'dbo', table_name 'Attributes'); ALTER FOREIGN TABLE schema.table OWNER TO owner;

I recreated the same foreign table, but this time using the query parameter instead: CREATE FOREIGN TABLE schema.table (attributeid character varying(32) , name character varying(64) , typeid character varying(32) ) SERVER foreign_server OPTIONS (query 'SELECT attributeid, name, typeid FROM dbo.Attributes'); And with this method it manages to read the table. It is however rather annoying to create foreign tables this way since you need to define the column names twice and some tables have a big amount of columns. Also opening the properties of a foreign table created with the query parameter crashes my pgAdmin3 client for some reason.

Thanks.

[GeoffMontee]

Thanks for the bug report!

Which version of tds_fdw are you using? Latest commit of the master branch? 2.0.0-alpha1? 1.0.7?

Can you please tell me exactly what query you were executing when you saw this error message?

NOTICE: DB-Library notice: Msg #: 4145, Msg state: 1, Msg: an expression of non-boolean type specified in a context where a condition is expected, near 'AND'., Server: server\instance, Process: , Line 1, Level: 15

It sounds like the WHERE push-down code might not be functioning properly for the query that you are attempting to execute.

[naegleria]

tds_fdw.control file shows default_version = '2.0.0-alpha.1'. And it seems I was a bit hasty by writing that the data wrapper is unable to read the tables since a regular SELECT * FROM schema.table query works, just that issuing that from pgAdmin3's GUI buttons (View Data) doesn't work as it seems to indeed add a WHERE statement: STATEMENT: SELECT count(*) FROM schema.table WHERE false. It also seems weird to me that it does a count(*) when it's supposed to actually show me all the columns. On the other hand this error is not present with oracle_fdw, so I'm not sure if the issue is with pgadmin3 or with tds_fdw.

[GeoffMontee]

It does seem that MS SQL Server does not like that statement.

If I execute something like this against a tds_fdw foreign table:

select count(*) from datetime2 where false;

Then the deparse.c code from postgres_fdw that tds_fdw uses builds a query that looks like this:

SELECT NULL FROM dbo.datetime2 WHERE (false) AND (false)

MS SQL Server returns the following error with this query:

NOTICE:  DB-Library notice: Msg #: 4145, Msg state: 1, Msg: An expression of non-boolean type specified in a context where a condition is expected, near 'AND'., Server: mgaf1hvpnc, Process: , Line: 1, Level: 15

It seems that there are two different problems with this:

• SQL Server has no boolean data type, so "true" and "false" don't mean anything by default.
• A single literal can't be used as a boolean expression in SQL Server. For example, this returns a similar error:

SELECT NULL FROM dbo.datetime2 WHERE 1;

While this works:

SELECT NULL FROM dbo.datetime2 WHERE 1 = 1;

So I'll have to fix both of these issues.

[GeoffMontee]

Hi @naegleria,

I'm not entirely sure how it would be best to fix this bug. I can add some code in deparseConst to prevent constant booleans such as "true" and "false" from being pushed down to the MS SQL Server, but since I'm not entirely sure how people tend to represent booleans in MS SQL Server, I'm not sure what they should be replaced with.

Would it make sense to replace "true" with "1" and "false" with "0", or do you think that would cause additional problems for MS SQL Server?

Should tds_fdw not push down boolean constants to MS SQL Server at all?

[GeoffMontee]

Hi @naegleria,

In the latest commit, tds_fdw will no longer push-down boolean constants to the foreign server. Please let me know if you still have any problems with your query.

[naegleria]

Hi, Sorry for the late reply. The pgAdmin select queries now work without issues. As for your question, I have very little knowledge about MSSQL, so couldn't have answered it. Thank you for the fix.