[Bug]: Cross-DB system view query is not currently supported in Babelfish

[markrui3]

What happened?

SQL：SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'tempdb..#tmp_table')

In SQL Server, it works fine. But in Babelfish, it gives me the result: Cross-DB system view query is not currently supported in Babelfish.

I know it's because of this patch: https://github.com/babelfish-for-postgresql/babelfish_extensions/pull/1311. But I don't want to modify my client code, can Babelfish give the same result with SQL Server?

Version

BABEL_4_X_DEV (Default)

Extension

None

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

No response

Relevant log output

LOG:  Unmapped error found. Code: 16801924, Message: Cross-DB system view query is not currently supported in Babelfish., File: multidb.c, Line: 993, Context: babelfishpg_tsql

Code of Conduct

• [X] I agree to follow this project's Code of Conduct.

[shalinilohia50]

This is a known issue in Babelfish. Cross database queries for system views is only supported for sys schema, but not in case of dbo in Babelfish. [Only applicable to views where the view content is database dependent, i.e., sysobjects, syscolumns etc.]

SELECT * FROM tempdb..sysobjects resolves to SELECT * FROM tempdb.dbo.sysobjects that's why Babelfish throws an error since it's querying dbo schema but not sys schema for system view sysobjects.

As a workaround, please use SELECT * FROM tempdb.sys.sysobjects until we fix the issue.

[markrui3]

But in SQL Server, tempdb.sys.sysobjects and tempdb.dbo.sysobjects return the same result. Why must Babelfish throw an error since dbo schema and sys schema return the same result? At least, sysobjects view is same in both dbo and sys schema in SQL Server.

[shalinilohia50]

Yes, it's a known bug in Babelfish that in case of cross database query using dbo schema for system view was giving incorrect results. So, until we fix this bug we have planned to throw an error which is better than showing incorrect output.

[markrui3]

Perhaps you are right, cross database query should use sys schema. But in SQL Server, the sys.sysobjects and dbo.sysobjects return the same result exactly! Customers don't care about dbo schema or sys schema, they only care about if the result is same with SQL Server. @robverschoor Please give a more professional explanation about this issue. I don't think it's a good idea to hard coding to forbid cross-db query.

[robverschoor]

shalinilohia50 is correct: this is an unfortunate bug in Babelfish as the sys and dbo schemas should be interchangeable for those catalogs. We all agree that the current result is not what it should be. The philosophy in the Babelfish project has always been that we prefer to throw an error rather than return an incorrect result.

[markrui3]

But in my SQL Server, SELECT * FROM tempdb.dbo.sysobjects and SELECT * FROM tempdb.sys.sysobjects return the same result. Maybe it's allowed to be interchangeable?

[robverschoor]

yes, they are equivalent. this is because in the older days, the sys schema did not exist and the catalogs were under the dbo schema. At some point schemas (and the sys schema for catalogs) were introduced but dbo.sys<catalog> was maintained, and equivalent to to sys.sys<catalog> for backward compatibility. As mentioned, Babelfish does not currently get this right.