search

babelfish-for-postgresql / babelfish_extensions

Babelfish for PostgreSQL provides the capability for PostgreSQL to work with applications written for Microsoft SQL Server. Babelfish understands the SQL Server wire-protocol and T-SQL, the Microsoft SQL Server query and procedural language, so you don’t have to switch database drivers or rewrite all of your application queries.
https://babelfishpg.org/
Apache License 2.0
265 stars 87 forks source link

[Bug]: TDS Column MetaData is different and looks incorrect #2402

Open dcowan-e-courier opened 4 months ago

dcowan-e-courier commented 4 months ago

What happened?

I have noticed ADO.net rowsets do not behave correctly when trying to do batch operations. Also inspecting rowset xml differs between mssql and babelfish. Key columns are not identified as key columns. I believe this is due to the TDS metadata for columns.

For a query like

select RateGroupID,Description from RateGroup with (readuncommitted) order by Description

I get TDS data like so image

For babelfish 3.4 I get this result

image

Notice many flags are missing. Also notice the Identity Flag is always true even for non identity columns. Also Updateable is false for babelfish

ADO.net connections seem to rely heavily on the column metadata and its correctness.

Version

BABEL_3_X_DEV (Default)

Extension

None

Which flavor of Linux are you using when you see the bug?

Ubuntu (Default)

Relevant log output

No response

Code of Conduct

staticlibs commented 1 week ago

Hi,

I was investigating this metadata issue:

Notice many flags are missing.

Out of all flags:

Babelfish currently supports setting the first 5 flags. Though, due to #232 it only sets these flags when FMTONLY ON is set. For ordinary queries it always returns constant value 0x9 (0b1001):

Also notice the Identity Flag is always true even for non identity columns.

It seems that Wireshark parser for this field is unreliable, on the screenshot above with babelfish 3.4 value is 0x0009, so Identity flag (5-th bit) is actually set to false.

Also Updateable is false for babelfish

Updatable flag is set to updatable unknown, that is likely to be interpreted as true by client driver (at least in mssql-jdbc).

Key columns are not identified as key columns.

M, according to fKey spec: "Its value is 1 if the column is part of a primary key for the row and the T-SQL SELECT statement contains FOR BROWSE.". Does the problematic query/tool operation use FOR BROWSE (not supported in Babelfish)?

I have noticed ADO.net rowsets do not behave correctly when trying to do batch operations. ADO.net connections seem to rely heavily on the column metadata and its correctness.

Is it possible to get an example of queries/tool operations that are failing due to incorrect metadata? Out of all flags it seems that only fIdentity and fComputed are applicable to Babelfish (fNullable seems to be problematic as described in #232).