search

tds-fdw / tds_fdw

A PostgreSQL foreign data wrapper to connect to TDS databases (Sybase and Microsoft SQL Server)
Other
381 stars 102 forks source link

The SQL query problem #86

Open sugerdudu opened 8 years ago

sugerdudu commented 8 years ago

SQL statement as follow： one: SELECT * FROM pro ORDER BY id DESC

USE "SQL Server Profiler" monitoring as follow result: SELECT * FROM pro ORDER BY id DESC NULLS FIRST

There is a NULLS FIRST but syntax error

two: select id, username from manage where username='a' USE "SQL Server Profiler" monitoring as follow result: SELECT id, username FROM dbo.manage WHERE ((username = 'a'::text)) AND ((username = 'a'::text))

There is a ::text but syntax error too

Smtgr14 commented 8 years ago

One: T-SQL does not support NULLS First or NULLS Last. I assuming by

USE "SQL Server Profiler" monitoring as follow result:

that you are viewing the query on SQL server? Try SELECT * FROM (SELECT * FROM pro) ORDER BY id NULLS FIRST This will force postgresql to send only SELECT * FROM pro to SQL Serve

Two: SELECT id, username FROM dbo.manage WHERE ((username = 'a'::text)) AND ((username = 'a'::text)) Obviously the casting "::" in unacceptable in T-SQL. Can you provide the table definition from the postgresql and the SQL server side? It looks like there might be a type mismatch that's causing this casting.

GeoffMontee commented 8 years ago

The NULLS FIRST issue was likely fixed with commit a4a98d81eac4c621596566cd33958ff89af0d37c.

The ::text issue was likely fixed with commit 209fad0099505f0b238b3dd424b91ac3946ed959 and 708036c0348b3431e54cb6c76a76deeff7297474.

These fixes are not available in a 2.0 release yet. Please use the master branch from the git repo for now.