tds-fdw / tds_fdw

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

select on a timestamp field throws date field value out of range: 164934532-164934516-164934500 #160

Open karlis-dreizis opened 6 years ago

karlis-dreizis commented 6 years ago

I'm running tds_fdw 2.0 alpha-2. (Had trouble with both 1.7 and 1.8), postgres 9.6.8

When I select a timestamp without time zone field I get a date field value out of range error message.

elximport=# select * from remote.News;
NOTICE:  tds_fdw: Query executed correctly
NOTICE:  tds_fdw: Getting results
ERROR:  date field value out of range: 165714844-165714828-165714812

I've edited /etc/freetds/locales.conf but that doesn't seem to make any difference. Aside from changing tsql's timestamp format.

[default]
        ;date format = %b %d %Y %I:%M%p
        date format = %Y-%m-%d %H:%M:%S
[en_US]
        ;date format = %b %d %Y %I:%M%p
        date format = %Y-%m-%d %H:%M:%S
        charset = iso_1

Though it seems that postgres itself is perfectly capable of handling both formats

elximport=# select '2017-06-22 00:00:00.888' :: timestamp;
        timestamp
-------------------------
 2017-06-22 00:00:00.888
(1 row)
elximport=# select 'May 19 2017 12:00AM' :: timestamp;
      timestamp
---------------------
 2017-05-19 00:00:00
karlis-dreizis commented 6 years ago

I tried casting the selected value to text in the select close and by altering the foreign table's date field's column type. With no change.

SELECT date :: text FROM remote.News; -- date out of range

Is it possible to somehow log what the FDW has fetched? It seems that the error is thrown in the fdw before postgres.

I set message handler to notice, but that doesn't tell you much. (..., msg_handler 'notice')

import=# select * from remote."News" ; NOTICE: DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'powerall'., Server: ZEVS2012, Process: , Line: 1, Level: 0 NOTICE: DB-Library notice: Msg #: 5703, Msg state: 1, Msg: Changed language setting to us_english., Server: ZEVS2012, Process: , Line: 1, Level: 0 NOTICE: tds_fdw: Query executed correctly NOTICE: tds_fdw: Getting results NOTICE: DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'powerall'., Server: ZEVS2012, Process: , Line: 1, Level: 0 NOTICE: DB-Library notice: Msg #: 5703, Msg state: 1, Msg: Changed language setting to us_english., Server: ZEVS2012, Process: , Line: 1, Level: 0 ERROR: date field value out of range: 167297500-167297484-167297468

GeoffMontee commented 6 years ago

If you rebuild tds_fdw with DEBUG defined, then it is a lot more verbose. You can see the symbol commented out here:

https://github.com/tds-fdw/tds_fdw/blob/13bc873bba674248d221c6d9b560ff71b70f01e4/src/tds_fdw.c#L71