timestamp 'without' problem

[viras777]

I think that I found a bug. SQL: db=# WITH dt AS ( SELECT COALESCE(t.max, def.value) AS last_sync FROM (VALUES ('2018-04-30'::date)) AS def(value), (SELECT max(pass_from_factory) FROM main_workers_presence_at_work) t ) SELECT s.rowid, d."DeviceTime"::timestamp without time zone FROM dt, sys_users_sbis s, ref_main_production_shop m, orion."pLogData" d, orion."pList" l WHERE l."ID" = d."HozOrgan" AND d."Mode" = 2 AND d."Event" = 32 AND d."Par3" = 5 AND l."TabNumber"::int = s.regnum AND s.top_department_id = m.department_id AND d."DeviceTime" > dt.last_sync limit 10;

get error: NOTICE: DB-Library notice: Msg #: 102, Msg state: 1, Msg: Incorrect syntax near 'without'., Server: ORION, Process: , Line: 1, Level: 15 ERROR: DB-Library error: DB #: 20018, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 15

BUT, if "AND d."DeviceTime" > dt.last_sync" change to "AND d."DeviceTime"::text > dt.last_sync::text" all OK or if add "order by 2" all OK.

version: MSSQL - 2005 PostgreSQL 9.6.8 on amd64-portbld-freebsd12.0, compiled by FreeBSD clang version 6.0.0 (tags/RELEASE_600/final 326565) (based on LLVM 6.0.0), 64-bit freetds - 1.00.365 FreeBSD DB-clstr1 12.0-CURRENT FreeBSD 12.0-CURRENT #3 r332502: Mon Apr 16 13:06:16 MSK 2018 gsh@DB:/usr/obj/usr/src/amd64.amd64/sys/kernel amd64

[SudoerWithAnOpinion]

(Cleaning this up for readability...)

I think that I found a bug. SQL:

WITH dt AS
(
       SELECT COALESCE(t.max, def.value) AS last_sync
       FROM   (VALUES
              (
                     '2018-04-30'::date
              )
              ) AS def(value),
              (
                     SELECT Max(pass_from_factory)
                     FROM   main_workers_presence_at_work) t )
SELECT s.rowid,
       d."DeviceTime"::timestamp without time zone
FROM   dt,
       sys_users_sbis s,
       ref_main_production_shop m,
       orion."pLogData" d,
       orion."pList" l
WHERE  l."ID" = d."HozOrgan"
      AND    d."Mode" = 2
      AND    d."Event" = 32
      AND    d."Par3" = 5
      AND    l."TabNumber"::int = s.regnum
      AND    s.top_department_id = m.department_id
      AND    d."DeviceTime" > dt.last_sync limit 10;

get error:

NOTICE: DB-Library notice: Msg #: 102, Msg state: 1, Msg: Incorrect syntax near 'without'., Server: ORION, Process: , Line: 1, Level: 15
ERROR: DB-Library error: DB #: 20018, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 15

BUT, if "AND d."DeviceTime" > dt.last_sync" change to "AND d."DeviceTime"::text > dt.last_sync::text" all OK or if add "order by 2" all OK.

version:

MSSQL - 2005
PostgreSQL 9.6.8 on amd64-portbld-freebsd12.0, compiled by FreeBSD clang version 6.0.0 (tags/RELEASE_600/final 326565) (based on LLVM 6.0.0), 64-bit
freetds - 1.00.365
FreeBSD DB-clstr1 12.0-CURRENT FreeBSD 12.0-CURRENT #3 r332502: Mon Apr 16 13:06:16 MSK 2018 gsh@DB:/usr/obj/usr/src/amd64.amd64/sys/kernel amd64

[SudoerWithAnOpinion]

It looks like a deparse issue, but you said casting using ::TEXT fixes it? Currently using tds_fdw with MSSQL 2005, I can tell you it's its own beast.

Can I see the foreign table definitions (and the definition on the source DB)? Or is this query the definition?

[GeoffMontee]

Weird. It looks like tds_fdw is casting the column to "timestamp without time zone" on the remote server, but this should actually be mapped to "datetime2" on the remote server. See the following:

https://github.com/tds-fdw/tds_fdw/blob/458df40ec8e006ed6f25e19d860be266f1b4f4fc/src/deparse.c#L712

[viras777]

Postgres:

CREATE FOREIGN TABLE orion."pLogData" (
  "TimeVal" TIMESTAMP(3) WITHOUT TIME ZONE OPTIONS (column_name 'TimeVal') NOT NULL,
  "NumCom" INTEGER OPTIONS (column_name 'NumCom'),
  "IDComp" INTEGER OPTIONS (column_name 'IDComp'),
  "Par1" INTEGER OPTIONS (column_name 'Par1'),
  "Par2" INTEGER OPTIONS (column_name 'Par2'),
  "Par3" INTEGER OPTIONS (column_name 'Par3'),
  "Par4" INTEGER OPTIONS (column_name 'Par4'),
  "Event" INTEGER OPTIONS (column_name 'Event') NOT NULL,
  "IndexKey" INTEGER OPTIONS (column_name 'IndexKey'),
  "RazdIndex" INTEGER OPTIONS (column_name 'RazdIndex'),
  "HozOrgan" INTEGER OPTIONS (column_name 'HozOrgan'),
  "HozGuest" INTEGER OPTIONS (column_name 'HozGuest'),
  "Remark" VARCHAR(50) OPTIONS (column_name 'Remark'),
  "DoorIndex" INTEGER OPTIONS (column_name 'DoorIndex'),
  "Mode" INTEGER OPTIONS (column_name 'Mode'),
  "DeviceTime" TIMESTAMP(3) WITHOUT TIME ZONE OPTIONS (column_name 'DeviceTime'),
  "VEvent" INTEGER OPTIONS (column_name 'VEvent'),
  "ZReserv" INTEGER OPTIONS (column_name 'ZReserv'),
  "ZoneIndex" INTEGER OPTIONS (column_name 'ZoneIndex'),
  "ReaderIndex" INTEGER OPTIONS (column_name 'ReaderIndex'),
  "Sign" INTEGER OPTIONS (column_name 'Sign'),
  "tpRzdIndex" INTEGER OPTIONS (column_name 'tpRzdIndex'),
  "tpPar4" INTEGER OPTIONS (column_name 'tpPar4'),
  "IndexZone" INTEGER OPTIONS (column_name 'IndexZone'),
  "tpIndex" INTEGER OPTIONS (column_name 'tpIndex'),
  "GUID" TEXT OPTIONS (column_name 'GUID') NOT NULL
) 
SERVER orion
OPTIONS (
  schema_name 'dbo',
  table_name 'pLogData');

MSSQL:

CREATE TABLE [dbo].[pLogData](
	[TimeVal] [datetime] NOT NULL,
	[NumCom] [int] NULL,
	[IDComp] [int] NULL,
	[Par1] [int] NULL,
	[Par2] [int] NULL,
	[Par3] [int] NULL,
	[Par4] [int] NULL,
	[Event] [int] NOT NULL,
	[IndexKey] [int] NULL,
	[RazdIndex] [int] NULL,
	[HozOrgan] [int] NULL,
	[HozGuest] [int] NULL,
	[Remark] [varchar](50) NULL,
	[DoorIndex] [int] NULL,
	[Mode] [int] NULL,
	[DeviceTime] [datetime] NULL,
	[VEvent] [int] NULL,
	[ZReserv] [int] NULL,
	[ZoneIndex] [int] NULL,
	[ReaderIndex] [int] NULL,
	[Sign] [int] NULL,
	[tpRzdIndex] [int] NULL,
	[tpPar4] [int] NULL,
	[IndexZone] [int] NULL,
	[tpIndex] [int] NULL,
	[GUID] [uniqueidentifier] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[GUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

[ghost]

@GeoffMontee, I just noticed the casting is to datetime2, but 2005 does not have this type. It should be mapped to datetime. Is there an easy way to fix this one?