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

MSSQL error when there is a dot in the query statement #277

Open zhaoguobin opened 3 years ago

zhaoguobin commented 3 years ago

Issue report

MSSQL error when there is a dot in the query statement.

OK:

SELECT  "standard_parts".* FROM "standard_parts" WHERE "standard_parts"."number" ILIKE '%b%' LIMIT $1 OFFSET $2

ERROR:

SELECT  "standard_parts".* FROM "standard_parts" WHERE "standard_parts"."number" ILIKE '%b\.%' LIMIT $1 OFFSET $2

Error message:

 (PG::FdwUnableToCreateExecution: 错误:  DB-Library error: DB #: 20018, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 15
: SELECT  "standard_parts".* FROM "standard_parts" WHERE "standard_parts"."number" ILIKE '%b\.%' LIMIT $1 OFFSET $2)

Operating system

NAME="openSUSE Leap"
VERSION="15.2"
ID="opensuse-leap"
ID_LIKE="suse opensuse"
VERSION_ID="15.2"
PRETTY_NAME="openSUSE Leap 15.2"
ANSI_COLOR="0;32"
CPE_NAME="cpe:/o:opensuse:leap:15.2"
BUG_REPORT_URL="https://bugs.opensuse.org"
HOME_URL="https://www.opensuse.org/"

Version of tds_fdw

---------+------+------------+------------------------------
 plpgsql | 1.0  | pg_catalog | PL/pgSQL procedural language

Built the package from Git:

commit 012350f68cea6159d1667b30d939afed05399e4a HEAD (HEAD -> master, origin/master, origin/HEAD)
Author: RAFAELDEV2016 <rafael.dev2016@gmail.com>
Date:   Fri Nov 27 21:44:34 2020 +0100

Version of PostgreSQL

                                        version
---------------------------------------------------------------------------------------
 PostgreSQL 10.15 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) 7.5.0, 64-bit

Version of FreeTDS

freetds-tools-1.1.36-lp152.1.1.x86_64
freetds-devel-1.1.36-lp152.1.1.x86_64
freetds-config-1.1.36-lp152.1.1.x86_64

Logs

Postgresql log:

2021-01-10 11:19:03.182 CST parts_management_production [4901]语句:  SELECT  "standard_parts".* FROM "standard_parts" WHERE "standard_parts"."standard_info" ILIKE '%GB 879\.2%' LIMIT $1 OFFSET $2
2021-01-10 11:19:11.089 CST parts_management_production [4921]警告:  Table definition mismatch: Foreign source has column named , but target table does not. Column will be ignored.
2021-01-10 11:19:31.969 CST parts_management_production [4921]错误:  DB-Library error: DB #: 20018, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 15

Sentences, data structures, data

CREATE FOREIGN TABLE standard_parts (
        number varchar(30) OPTIONS (column_name 'xxx'),
        standard_info varchar(30) OPTIONS(column_name 'xxx'),
        ......
      SERVER mssql_svr
      OPTIONS (schema_name 'xxx', table_name 'xxx', row_estimate_method 'showplan_all');