Overview

Babelfish 5.4.0 contains enhancements and fixes that improve the user experience, including:

New Features

• Enabled support of SELECT TOP N PERCENT clause with few limitations
  • SELECT operations are supported while UPDATE, DELETE and INSERT operations with TOP N PERCENT are not supported. The WITH TIES option and subqueries within the TOP clause are also not supported.
  • When the expression value exceeds 100, Babelfish treats the value as valid and returns results.
• Enabled support for Linestring instances for geography/geometry datatypes.
• Enabled support for system procedures sp_xml_preparedocument, sp_xml_removedocument and OPENXML .
• Enabled support for XML method .VALUE() for XML Data Types
• Enabled support for ownership chaining for object references inside views and stored procedures/functions. Permission checking on underlying objects is determined by either the user's direct permissions or through ownership chaining.
• Enabled support of sys.time_zone_info view.
• Enabled support for Values clause and subquery columns in SELECT list in FOR JSON AUTO functionality.

Critical enhancements

• Fixed STPointFromText() and Point() to throw error on NULL arguments
• Fixed issues with Geometry/Geography to (var)char and (var)binary conversions.
• Fixed function definition of STPointFromText(), STPointFromText(), STAsText() and STAsBinary().
• Fixed bytea to Geometry/Geography conversion for Point instance.
• ASCII function returns incorrect results with argument type as Binary and Varbinary
• Active snapshots when system catalogs are being updated while ResetTempTableNamespace in TDS
• Fixed crashes and intermittent errors caused by memory corruption when a parameter is assigned to itself.

High Priority stability enhancements

• Fixed an issue where user can not be dropped when permissions have been granted to that user.
• Added support for the planner to choose index scan for queries having predicates comparing numeric and money/smallmoney data types.
• Fixed precision/scale for Round() function.
• Fixed an issue where rollback to savepoint in some cases failed to send the correct transaction state token to the client, causing subsequent operations in the transaction to fail.
• Fixed an issue where errors in pg_cron job was leading to server reboot

Additional improvements and enhancements

• Fixed an issue to allow concurrent UPDATE operations with OUTPUT clauses.
• Fixed an issue which resets the escape hatch settings to default with the first temp table creation
• Fixed Datetime and Varbinary to Binary CAST functions.
• Fixed an issue to match CONVERT and CAST function return values for Binary type
• Fixed INSERT INTO table DEFAULT VALUES for domain types, by setting appropriate function return type.
• Fixed an issue where running multi-Statement table valued function throws syntax error if the database name contains a symbol for an operator.
• Fixed REVOKE permission execution to correctly handle overlapping schema-level and object-level grants.
• Enabled support for the NULL/NOT NULL syntax in ALTER TABLE ALTER COLUMN statements
• Add identifier delimiters to handle special characters in login and role names
• Fixed indexes that were created on top of T-SQL temporary tables to correctly follow transaction semantics
• Fixed various mathematical functions for Money and smallmoney datatype
• Fixed an issue to persist column level permissions when modifying views using ALTER VIEW
• Enabled support to allow alter on functions with dependent weak schemabinding views.
• Fixed issue with Geometry/Geography static methods when using delimited identifiers for data types.
• Fixed return type of SQRT() function to float
• Fixed issues with assignment of multibyte characters to local variable of type nchar/nvarchar
• Added empty string handling in ISNUMERIC(), to ensure compatibility with T-SQL behavior.
• Fixed DATALENGTH() to return correct byte length value for various datatypes and their UDTs.
• Fixed DATETIME to VARBINARY CAST function
• Fixed precision/scale in SELECT INTO while using with GROUP BY clause
• Fixed an issue of protocol error in TDS stream due to Numeric/Decimal overflow
• Fixed issues with CTE logic and missing intermediate levels in the output of FOR JSON AUTO clause
• Fixed an issue related to LIKE operator where ESCAPE character does not work as expected when ESCAPE character comes after wildcard in pattern
• Fixed an issue which occurs when parameter of type binary(8) passed from .NET driver.

Upgrading to the new version

If you’re running Babelfish in a managed environment, please check your vendor’s instructions for upgrading.

MINOR VERSION UPGRADE

Babelfish 5.4.0 is compatible with 5.3.0, 5.2.0, 5.1.0 version of Babelfish; a dump/restore is not required to upgrade to the newer version from previous releases. To upgrade:

1. Install the new binaries; for details, see [Compiling Babelfish From Source] (https://babelfishpg.org/docs/installation/compiling-babelfish-from-source/).
2. Start the new server.
3. Update the modified extensions.

To update the extensions that have changed since the last release, connect to the PostgreSQL endpoint of the Babelfish database and execute the following statements from a single session in this order:

ALTER EXTENSION "babelfishpg_common" UPDATE;
ALTER EXTENSION "babelfishpg_tsql" UPDATE;

Please note that the extension owner must invoke ALTER EXTENSION. For this release, the babelfish_tds and babelfish_money extensions don’t include any SQL-level changes and need not be updated.
After the extensions are updated, you can check the extension versions from the PostgreSQL port:

babelfish_db=# \dx
List of installed extensions
Name        | Version |   Schema   |   Description
--------------------+---------+------------+-------------------------------------------------
babelfishpg_common | 5.4.0   | public     | Transact SQL Datatype Support
....
babelfishpg_tsql   | 5.4.0   | public     | Transact SQL Compatibility
....

MAJOR VERSION UPGRADE

Babelfish 5.4.0 provides the ability to upgrade from some 4.x.x, 3.x.x and 2.x.x versions of Babelfish. This process is called major version upgrade (MVU). It consists of two steps. First, you need to perform a major version upgrade on the PostgreSQL server to upgrade it to the target version. Second, you need to update all Babelfish extensions to the target version. Currently, Babelfish only supports upgrade using the pg_upgrade module. Other upgrade methods such as dump/restore and replication are not supported.

Overview

Babelfish 4.8.0 contains enhancements and fixes that improve the user experience, including:

New Features

• Enabled support of SELECT TOP N PERCENT clause with few limitations
  • SELECT operations are supported while UPDATE, DELETE and INSERT operations with TOP N PERCENT are not supported. The WITH TIES option and subqueries within the TOP clause are also not supported.
  • When the expression value exceeds 100, Babelfish treats the value as valid and returns results.
• Enabled support of sys.time_zone_info view
• Enabled support for Values clause and subquery columns in SELECT list in FOR JSON AUTO functionality

Critical enhancements

• Fixed STPointFromText() and Point() to throw error on NULL arguments
• Fixed issues with Geometry/Geography to (var)char and (var)binary conversions.
• Fixed function definition of STPointFromText(), STPointFromText(), STAsText() and STAsBinary().
• Fixed bytea to Geometry/Geography conversion for Point instance.
• Fixed handling of STPointFromText, STGeomFromText, STLineFromText for incorrect binary input
• ASCII function returns incorrect results with argument type as Binary and Varbinary
• Active snapshots when system catalogs are being updated while ResetTempTableNamespace in TDS
• Fixed crashes and intermittent errors caused by memory corruption when a parameter is assigned to itself.

High Priority stability enhancements

• Fixed an issue where user can not be dropped when permissions have been granted to that user
• Added support for the planner to choose index scan for queries having predicates comparing numeric and money/smallmoney data types
• Fixed precision/scale for Round() function
• Fixed an issue where rollback to savepoint in some cases failed to send the correct transaction state token to the client, causing subsequent operations in the transaction to fail.
• Fixed an issue where errors in pg_cron job was leading to server reboot

Additional improvements and enhancements

• Fixed an issue to allow concurrent UPDATE operations with OUTPUT clauses.
• Fixed an issue which resets the escape hatch settings to default with the first temp table creation
• Fixed Datetime and Varbinary to Binary CAST functions.
• Fixed an issue to match CONVERT and CAST function return values for Binary type
• Fixed INSERT INTO table DEFAULT VALUES for domain types, by setting appropriate function return type.
• Fixed an issue where running multi-Statement table valued function throws syntax error if the database name contains a symbol for an operator.
• Fixed REVOKE permission execution to correctly handle overlapping schema-level and object-level grants.
• Enabled support for the NULL/NOT NULL syntax in ALTER TABLE ALTER COLUMN statements
• Add identifier delimiters to handle special characters in login and role names
• Fixed indexes that were created on top of T-SQL temporary tables to correctly follow transaction semantics
• Fixed various mathematical functions for Money and smallmoney datatype
• Fixed an issue to persist column level permissions when modifying views using ALTER VIEW
• Enabled support to allow alter on functions with dependent weak schemabinding views.
• Fixed issue with Geometry/Geography static methods when using delimited identifiers for data types.
• Fixed return type of SQRT() function to float
• Fixed issues with assignment of multibyte characters to local variable of type nchar/nvarchar
• Added empty string handling in ISNUMERIC(), to ensure compatibility with T-SQL behavior.
• Fixed DATALENGTH() to return correct byte length value for various datatypes and their UDTs.
• Fixed DATETIME to VARBINARY CAST function
• Fixed precision/scale in SELECT INTO while using with GROUP BY clause
• Fixed an issue of protocol error in TDS stream due to Numeric/Decimal overflow
• Fixed issues with CTE logic and missing intermediate levels in the output of FOR JSON AUTO clause
• Fixed an issue related to LIKE operator where ESCAPE character does not work as expected when ESCAPE character comes after wildcard in pattern
• Fixed an issue which occurs when parameter of type binary(8) passed from .NET driver.

Upgrading to the new version

If you’re running Babelfish in a managed environment, please check your vendor’s instructions for upgrading.

MINOR VERSION UPGRADE

Babelfish 4.8.0 is compatible with 4.x.x versions of Babelfish; a dump/restore is not required to upgrade to the newer version from previous releases. To upgrade:

1. Install the new binaries; for details, see [Compiling Babelfish From Source] (https://babelfishpg.org/docs/installation/compiling-babelfish-from-source/).
2. Start the new server.
3. Update the modified extensions.

To update the extensions that have changed since the last release, connect to the PostgreSQL endpoint of the Babelfish database and execute the following statements from a single session in this order:

ALTER EXTENSION "babelfishpg_common" UPDATE;
ALTER EXTENSION "babelfishpg_tsql" UPDATE;

Please note that the extension owner must invoke ALTER EXTENSION. For this release, the babelfish_tds and babelfish_money extensions don’t include any SQL-level changes and need not be updated.
After the extensions are updated, you can check the extension versions from the PostgreSQL port:

babelfish_db=# \dx
List of installed extensions
Name        | Version |   Schema   |   Description
--------------------+---------+------------+-------------------------------------------------
babelfishpg_common | 4.8.0   | public     | Transact SQL Datatype Support
....
babelfishpg_tsql   | 4.8.0   | public     | Transact SQL Compatibility
....

MAJOR VERSION UPGRADE

Babelfish 4.8.0 provides the ability to upgrade from some 3.x.x and 2.x.x versions of Babelfish. This process is called major version upgrade (MVU). It consists of two steps. First, you need to perform a major version upgrade on the PostgreSQL server to upgrade it to the target version. Second, you need to update all Babelfish extensions to the target version. Currently, Babelfish only supports upgrade using the pg_upgrade module. Other upgrade methods such as dump/restore and replication are not supported.

Overview

Babelfish 5.3.0 contains enhancements and fixes that improve the user experience, including:

New Features

• Added support for boolean operators and prefix term grammer in T-SQL CONTAINS clause for Full-Text Search.
• Added support for Z-M flags for Point Instances and Z, M, HasZ and HasM functions for GEOMETRY and GEOGRAPHY datatypes.
• Added support for sp_helplogins stored procedure
• Enabled Support for weak binding views.

Critical enhancements

• Fixed an issue during TDS Reset Connection in certain situations
• Optimized the Antlr parser performance
• Improved the performance of queries using the ISNUMERIC() function in predicates
• Added support to let index scan used in case of queries having predicates comparing numeric and integer data types

High Priority stability enhancements

• Fixed overflow checks in all arithmetic operations with money/smallmoney being a higher precedence operand
• Fixed the output datatype for all arithmetic operations involving smallmoney and bit
• Fixed behavior of certain math functions like CEIL, ROUND, POWER and FLOOR for money and smallmoney data types
• Fixed issue with connection crash during arithmetic operations on smallmoney and int
• Fixed overflow checks in all arithmetic operations with smallmoney/money being a operand
• Fixed an issue with DATENAME() to gives correct value of TZOFFSET part
• Handling for CHAR()/NCHAR() function to return NULL instead of errors when values are out of range.
• Handling for NCHAR() function to accept inputs that can be converted to integers
• Fixed PATINDEX() function to correctly finds patterns at the end of text and handles wildcard searches accurately.
• Enabled index usage for Accent Sensitive / Insensitive collation for LIKE operator when the pattern is EXACT or PREFIX or INFIX match with following conditions:
  • For Accent Sensitive collation, user needs to create index from TSQL endpoint : CREATE INDEX <index_name> ON <table_name>(<column_name>)
  • For Accent Insensitive collation, user needs to create index from PSQL endpoint: CREATE INDEX <index_name> ON <schema_name>.<table_name>(sys.remove_accents_internal_using_cache(<column_name>))
• Fixed an issue where TRY_CAST and TRY_CONVERT was incorrectly rounding decimal values when casting to integer types, instead of truncating the fractional part. (Contributed by bilalkah)
• Fixed precision and scale for money/small money and numeric for various scenarios.
• Fixed an issue with RESET ALL command from postgres endpoint
• Fixed an issue with response packets when reading large nvarchar(max) data, which could cause ArgumentOutOfRangeException with .NET driver.
• Fixed an issue where parallelism won't be used for pltsql RETURN expression
• Fixed permission denied issue in cross-db TVF
• Fixed an issue to preserve Timezone information during casting from string to datetimeoffset
• Fixed Function QUOTENAME() to return correct strings
• Added handling of binary arguments for Len() function
• Fixed the precision and scale for aggregate function that have *(all columns) as input.
• Fixed an issue for CaseExpr with numeric computation.
• Fixed a crash in queries using FOR JSON AUTO and JSON PATH
• Fixed rounding off issue during storing datetime datatype. Existing users should run the following query from TSQL endpoint to update their existing data: UPDATE <table_name> SET <datetime_col> = CAST(CAST(<datetime_col> as VARCHAR) AS DATETIME)

Additional improvements and enhancements

• Fixed an issue with OBJECT_DEFINITION function where it was trucating the output after 4000 characters.
• Handle PostgreSQL reserved keywords in Cursor operations
• Added support for sys.server_permissions, sys.sql_logins views and sys.fn_varbintohexsubstring system function.
• Fixed an issue while adding a column with default value which resulted in an error
• Fixed an issue with INSERT ... EXECUTE statements in stored procedures related to nested levels

Upgrading to the new version

If you’re running Babelfish in a managed environment, please check your vendor’s instructions for upgrading.

MINOR VERSION UPGRADE

Babelfish 5.3.0 is compatible with 5.2.0, 5.1.0 version of Babelfish; a dump/restore is not required to upgrade to the newer version from previous releases. To upgrade:

1. Install the new binaries; for details, see [Compiling Babelfish From Source] (https://babelfishpg.org/docs/installation/compiling-babelfish-from-source/).
2. Start the new server.
3. Update the modified extensions.

To update the extensions that have changed since the last release, connect to the PostgreSQL endpoint of the Babelfish database and execute the following statements from a single session in this order:

ALTER EXTENSION "babelfishpg_common" UPDATE;
ALTER EXTENSION "babelfishpg_tsql" UPDATE;

Please note that the extension owner must invoke ALTER EXTENSION. For this release, the babelfish_tds and babelfish_money extensions don’t include any SQL-level changes and need not be updated.
After the extensions are updated, you can check the extension versions from the PostgreSQL port:

babelfish_db=# \dx
List of installed extensions
Name        | Version |   Schema   |   Description
--------------------+---------+------------+-------------------------------------------------
babelfishpg_common | 5.3.0   | public     | Transact SQL Datatype Support
....
babelfishpg_tsql   | 5.3.0   | public     | Transact SQL Compatibility
....

MAJOR VERSION UPGRADE

Babelfish 5.3.0 provides the ability to upgrade from some 4.x.x, 3.x.x and 2.x.x versions of Babelfish. This process is called major version upgrade (MVU). It consists of two steps. First, you need to perform a major version upgrade on the PostgreSQL server to upgrade it to the target version. Second, you need to update all Babelfish extensions to the target version. Currently, Babelfish only supports upgrade using the pg_upgrade module. Other upgrade methods such as dump/restore and replication are not supported.

OpenSource Release Notes (for 5.2.0)

Overview

Babelfish 5.2.0 contains enhancements and fixes that improve the user experience, including:

New Features

• Added support for fixed database-level roles db_securityadmin, db_accessadmin, db_ddladmin, db_datareader and db_datawriter. T-SQL users can be added to these fixed database roles.
• Added support for fixed server-level roles securityadmin and dbcreator. T-SQL logins can be added to these fixed server roles.
• Added support for adding T-SQL users to db_owner fixed database-level role. This T-SQL will have privileges similar to the database owner.
• Added support for CREATE OR ALTER VIEW / ALTER VIEW syntax in Babelfish with the following limitations:
  1. Cannot be used on views that have INSTEAD-OF trigger attached
  2. Cannot be used on views that have another view based on this view
• Added support of Transact-SQL UNPIVOT operators for Babelfish
• Added support for STDimension, STDisjoint, STIntersects, STIsClosed, STIsEmpty, STIsValid Geospatial functions
• Added support for collation in partition functions and partitioning columns.
• Enabled support for scripting logins in SSMS for Babelfish

Critical enhancements

• Fixed issues with Convert function when converting from string to date, datetimeoffset, datetime2, datetime, smalldatetime and time.
• Supported SET IDENTITY_INSERT for three part relation references
• Fixed the issue of UPDATE/DELETE of table variable query in a function getting incorrectly rejected with multi-table FROM clause
• Fixed a crash which could occur in rare situations when using temporary tables with certain orphaned catalog entries.
• Fixed an issue where trigger gets dropped when dropping a column in a table
• Fix inconsistent formatting issue with Convert function when converting MONEY datatype with value 0 to string datatypes.
• Fix formatting issues in CAST from MONEY to CHAR/VARCHAR
• Fixed the issue where SELECT...INTO with MIN and MAX aggregations on MONEY columns lost type information.
• Fixed a issue with IDENTITY columns not being recognized during DML statements using OUTPUT and WHERE clause
• Fixed an issue with object ownership. Any new object created by a user from TDS endpoint will now be owned by schema owner as opposed to previous behavior where the current user always owned the new object.

High Priority stability enhancements

• Fixed an issue where @@function in UPDATE SET clause causes syntax error
• Fixed dynamic evaluation of @local_var for UPDATE ... SET @local_var and SELECT command
• Fixed an issue with sp_columns_100 where partial data could be returned if @fUsePattern = 0 is used
• Fixed the incorrect result datatype of UNION involving MONEY type
• Fixed offset when using 'AT TIME ZONE' with DATETIME2 datatype conversion with convert() function in non-default local timezone setting.
• Fixed an issue where batches containing cross database queries looks up the objects in incorrect database
• Fix behavior of queries which use sys.Db_id() function which returned empty rows in enforced parallel mode
• Fixed logic to adjust precision and scale in Numeric Multiplication and Division
• Fixed PIVOT operator to gracefully handle NULL entries in pivot column
• Fixed the logic for numeric/decimal datatype typmod resolution in outer/inner queries.
• Fixed bug which prevented enable/disable all triggers on tables
• Fixed issue with result having incorrect scale in numeric/decimal addition and subtraction
• Fixed issue which causes incorrect result in arithmetic operations which results in numeric/decimal type
• Fixed an issue which caused communication link failure with DROP LOGIN/USER/ROLE [public]
• Restricted dropping of system procedures and views from dbo schema
• Fixed an issue in round() function to ensure return types match input argument types
• Improved index name handling in Babelfish by preserving the original index name in the catalog, making it visible in catalog views like sys.indexes. It also adds support for renaming existing indexes using sp_rename to preserve and display their original names.
• Transfer nullability and identity properties of columns in SELECT INTO statement to new table. These properties are only transferred if there is a single table in FROM clause of SELECT INTO and the column is not part of expression in select into statements target list.

Additional improvements and enhancements

• Fixed an error in OPENJSON function call to allow parse on long JSON string
• Fix an issue with system procedure fn_listextendedproperty
• Fixed the use of table-valued parameters as arguments in procedures. Previously, you had to specify the type name of the table-valued parameter when calling the procedure, now it is optional.
• Fixed precision and scale when common type of CASE expression is NUMERIC / DECIMAL .
• Fixed an issue where sys.dm_exec_sessions may have abandoned entries for already terminated connections
• Fixed JSON string formatting to prevent improper backslash escaping in 'FOR JSON' output with json_query function
• Fixed sys.objects catalog to correctly populate unique constraints metadata
• Fixed issue when max length of a RPC character based parameter is 0
• Added an escape hatch for the INLINE option in CREATE FUNCTION statements, defaulting to 'strict' mode for proper error handling.

Upgrading to the new version

If you’re running Babelfish in a managed environment, please check your vendor’s instructions for upgrading.

MAJOR VERSION UPGRADE

Babelfish 5.2.0 provides the ability to upgrade from 4.x.x, 3.x.x and 2.x.x versions of Babelfish. This process is called major version upgrade (MVU). It consists of two steps. First, you need to perform a major version upgrade on the PostgreSQL server to upgrade it to the target version. Second, you need to update all Babelfish extensions to the target version. Currently, Babelfish only supports upgrade using the pg_upgrade module. Other upgrade methods such as dump/restore and replication are not supported.

Overview

Babelfish 4.7.0 contains enhancements and fixes that improve the user experience, including:

New Features

• Added support for Z-M flags for Point Instances and Z, M, HasZ and HasM functions for GEOMETRY and GEOGRAPHY datatypes.
• Added support for sp_helplogins stored procedure
• Enabled Support for weak binding views.

Critical enhancements

• Fixed an issue during TDS Reset Connection in certain situations
• Optimized the Antlr parser performance
• Improved the performance of queries using the ISNUMERIC() function in predicates
• Added support to let index scan used in case of queries having predicates comparing numeric and integer data types

High Priority stability enhancements

• Fixed overflow checks in all arithmetic operations with money/smallmoney being a higher precedence operand
• Fixed the output datatype for all arithmetic operations involving smallmoney and bit
• Fixed behavior of certain math functions like CEIL, ROUND, POWER and FLOOR for money and smallmoney data types
• Fixed issue with connection crash during arithmetic operations on smallmoney and int
• Fixed overflow checks in all arithmetic operations with smallmoney/money being a operand
• Fixed an issue with DATENAME() to gives correct value of TZOFFSET part
• Handling for CHAR()/NCHAR() function to return NULL instead of errors when values are out of range.
• Handling for NCHAR() function to accept inputs that can be converted to integers
• Fixed PATINDEX() function to correctly finds patterns at the end of text and handles wildcard searches accurately.
• Enabled index usage for Accent Sensitive / Insensitive collation for LIKE operator when the pattern is EXACT or PREFIX or INFIX match with following conditions:
  • For Accent Sensitive collation, user needs to create index from TSQL endpoint : CREATE INDEX <index_name> ON <table_name>(<column_name>)
  • For Accent Insensitive collation, user needs to create index from PSQL endpoint: CREATE INDEX <index_name> ON <schema_name>.<table_name>(sys.remove_accents_internal_using_cache(<column_name>))
• Fixed an issue where TRY_CAST and TRY_CONVERT was incorrectly rounding decimal values when casting to integer types, instead of truncating the fractional part. (Contributed by bilalkah)
• Fixed precision and scale for money/small money and numeric for various scenarios.
• Fixed an issue with RESET ALL command from postgres endpoint
• Fixed an issue with response packets when reading large nvarchar(max) data, which could cause ArgumentOutOfRangeException with .NET driver.
• Fixed an issue where parallelism won't be used for pltsql RETURN expression
• Fixed permission denied issue in cross-db TVF
• Fixed an issue to preserve Timezone information during casting from string to datetimeoffset
• Fixed Function QUOTENAME() to return correct strings
• Added handling of binary arguments for Len() function
• Fixed the precision and scale for aggregate function that have *(all columns) as input.
• Fixed an issue for CaseExpr with numeric computation.
• Fixed a crash in queries using FOR JSON AUTO and JSON PATH
• Fixed rounding off issue during storing datetime datatype. Existing users should run the following query from TSQL endpoint to update their existing data: UPDATE <table_name> SET <datetime_col> = CAST(CAST(<datetime_col> as VARCHAR) AS DATETIME)

Additional improvements and enhancements

• Fixed an issue with OBJECT_DEFINITION function where it was trucating the output after 4000 characters.
• Handle PostgreSQL reserved keywords in Cursor operations
• Added support for sys.server_permissions, sys.sql_logins views and sys.fn_varbintohexsubstring system function.
• Fixed an issue while adding a column with default value which resulted in an error
• Fixed an issue with INSERT ... EXECUTE statements in stored procedures related to nested levels

Upgrading to the new version

If you’re running Babelfish in a managed environment, please check your vendor’s instructions for upgrading.

MINOR VERSION UPGRADE

Babelfish 4.7.0 is compatible with 4.x.x versions of Babelfish; a dump/restore is not required to upgrade to the newer version from previous releases. To upgrade:

1. Install the new binaries; for details, see [Compiling Babelfish From Source] (https://babelfishpg.org/docs/installation/compiling-babelfish-from-source/).
2. Start the new server.
3. Update the modified extensions.

To update the extensions that have changed since the last release, connect to the PostgreSQL endpoint of the Babelfish database and execute the following statements from a single session in this order:

ALTER EXTENSION "babelfishpg_common" UPDATE;
ALTER EXTENSION "babelfishpg_tsql" UPDATE;

Please note that the extension owner must invoke ALTER EXTENSION. For this release, the babelfish_tds and babelfish_money extensions don’t include any SQL-level changes and need not be updated.
After the extensions are updated, you can check the extension versions from the PostgreSQL port:

babelfish_db=# \dx
List of installed extensions
Name        | Version |   Schema   |   Description
--------------------+---------+------------+-------------------------------------------------
babelfishpg_common | 4.7.0   | public     | Transact SQL Datatype Support
....
babelfishpg_tsql   | 4.7.0   | public     | Transact SQL Compatibility
....

MAJOR VERSION UPGRADE

Babelfish 4.7.0 provides the ability to upgrade from some 3.x.x and 2.x.x versions of Babelfish. This process is called major version upgrade (MVU). It consists of two steps. First, you need to perform a major version upgrade on the PostgreSQL server to upgrade it to the target version. Second, you need to update all Babelfish extensions to the target version. Currently, Babelfish only supports upgrade using the pg_upgrade module. Other upgrade methods such as dump/restore and replication are not supported.

OpenSource Release Notes (for 4.6.0)

Overview

Babelfish 4.6.0 contains enhancements and fixes that improve the user experience, including:

New Features

• Added support for fixed database-level roles db_securityadmin, db_accessadmin, db_ddladmin, db_datareader and db_datawriter. T-SQL users can be added to these fixed database roles.
• Added support for fixed server-level roles securityadmin and dbcreator. T-SQL logins can be added to these fixed server roles.
• Added support for adding T-SQL users to db_owner fixed database-level role. This T-SQL will have privileges similar to the database owner.
• Added support for CREATE OR ALTER VIEW / ALTER VIEW syntax in Babelfish with the following limitations:
  1. Cannot be used on views that have INSTEAD-OF trigger attached
  2. Cannot be used on views that have another view based on this view
• Added support of Transact-SQL UNPIVOT operators for Babelfish
• Added support for STDimension, STDisjoint, STIntersects, STIsClosed, STIsEmpty, STIsValid Geospatial functions
• Added support for collation in partition functions and partitioning columns.
• Enabled support for scripting logins in SSMS for Babelfish

Critical enhancements

• Fixed issues with Convert function when converting from string to date, datetimeoffset, datetime2, datetime, smalldatetime and time.
• Supported SET IDENTITY_INSERT for three part relation references
• Fixed the issue of UPDATE/DELETE of table variable query in a function getting incorrectly rejected with multi-table FROM clause
• Fixed a crash which could occur in rare situations when using temporary tables with certain orphaned catalog entries.
• Fixed an issue where trigger gets dropped when dropping a column in a table
• Fix inconsistent formatting issue with Convert function when converting MONEY datatype with value 0 to string datatypes.
• Fix formatting issues in CAST from MONEY to CHAR/VARCHAR
• Fixed the issue where SELECT...INTO with MIN and MAX aggregations on MONEY columns lost type information.
• Fixed a issue with IDENTITY columns not being recognized during DML statements using OUTPUT and WHERE clause
• Fixed an issue with object ownership. Any new object created by a user from TDS endpoint will now be owned by schema owner as opposed to previous behavior where the current user always owned the new object.

High Priority stability enhancements

• Fixed an issue where @@function in UPDATE SET clause causes syntax error
• Fixed dynamic evaluation of @local_var for UPDATE ... SET @local_var and SELECT command
• Fixed an issue with sp_columns_100 where partial data could be returned if @fUsePattern = 0 is used
• Fixed the incorrect result datatype of UNION involving MONEY type
• Fixed offset when using 'AT TIME ZONE' with DATETIME2 datatype conversion with convert() function in non-default local timezone setting.
• Fixed an issue where batches containing cross database queries looks up the objects in incorrect database
• Fix behavior of queries which use sys.Db_id() function which returned empty rows in enforced parallel mode
• Fixed logic to adjust precision and scale in Numeric Multiplication and Division
• Fixed PIVOT operator to gracefully handle NULL entries in pivot column
• Fixed the logic for numeric/decimal datatype typmod resolution in outer/inner queries.
• Fixed bug which prevented enable/disable all triggers on tables
• Fixed issue with result having incorrect scale in numeric/decimal addition and subtraction
• Fixed issue which causes incorrect result in arithmetic operations which results in numeric/decimal type
• Fixed an issue which caused communication link failure with DROP LOGIN/USER/ROLE [public]
• Restricted dropping of system procedures and views from dbo schema
• Fixed an issue in round() function to ensure return types match input argument types
• Improved index name handling in Babelfish by preserving the original index name in the catalog, making it visible in catalog views like sys.indexes. It also adds support for renaming existing indexes using sp_rename to preserve and display their original names.
• Transfer nullability and identity properties of columns in SELECT INTO statement to new table. These properties are only transferred if there is a single table in FROM clause of SELECT INTO and the column is not part of expression in select into statements target list.

Additional improvements and enhancements

• Fixed an error in OPENJSON function call to allow parse on long JSON string
• Fix an issue with system procedure fn_listextendedproperty
• Fixed the use of table-valued parameters as arguments in procedures. Previously, you had to specify the type name of the table-valued parameter when calling the procedure, now it is optional.
• Fixed precision and scale when common type of CASE expression is NUMERIC / DECIMAL .
• Fixed an issue where sys.dm_exec_sessions may have abandoned entries for already terminated connections
• Fixed JSON string formatting to prevent improper backslash escaping in 'FOR JSON' output with json_query function
• Fixed sys.objects catalog to correctly populate unique constraints metadata
• Fixed issue when max length of a RPC character based parameter is 0
• Added an escape hatch for the INLINE option in CREATE FUNCTION statements, defaulting to 'strict' mode for proper error handling.

Upgrading to the new version

If you’re running Babelfish in a managed environment, please check your vendor’s instructions for upgrading.

MINOR VERSION UPGRADE

Babelfish 4.6.0 is compatible with 4.x.x versions of Babelfish; a dump/restore is not required to upgrade to the newer version from previous releases. To upgrade:

1. Install the new binaries; for details, see [Compiling Babelfish From Source] (https://babelfishpg.org/docs/installation/compiling-babelfish-from-source/).
2. Start the new server.
3. Update the modified extensions.

To update the extensions that have changed since the last release, connect to the PostgreSQL endpoint of the Babelfish database and execute the following statements from a single session in this order:

ALTER EXTENSION "babelfishpg_common" UPDATE;
ALTER EXTENSION "babelfishpg_tsql" UPDATE;

Please note that the extension owner must invoke ALTER EXTENSION. For this release, the babelfish_tds and babelfish_money extensions don’t include any SQL-level changes and need not be updated.
After the extensions are updated, you can check the extension versions from the PostgreSQL port:

babelfish_db=# \dx
List of installed extensions
Name        | Version |   Schema   |   Description
--------------------+---------+------------+-------------------------------------------------
babelfishpg_common | 4.6.0   | public     | Transact SQL Datatype Support
....
babelfishpg_tsql   | 4.6.0   | public     | Transact SQL Compatibility
....

MAJOR VERSION UPGRADE

Babelfish 4.6.0 provides the ability to upgrade from 3.x.x and 2.x.x versions of Babelfish. This process is called major version upgrade (MVU). It consists of two steps. First, you need to perform a major version upgrade on the PostgreSQL server to upgrade it to the target version. Second, you need to update all Babelfish extensions to the target version. Currently, Babelfish only supports upgrade using the pg_upgrade module. Other upgrade methods such as dump/restore and replication are not supported.

Overview

Babelfish 4.4.0 contains enhancements and fixes that improve the user experience, including:

New features

• Added support for ALTER FUNCTION syntax.
• Added support for view usage for PIVOT operator.
• Added pgaudit extension support with Babelfish.
• Added support of XML method .EXIST() for XML Datatypes.
• Enabled support of Geospatial Data types in data migration via DMS for Babelfish under PostgreSQL endpoint.
• Enabled user to create database with specific collations.
• Enabled support of sys.sp_reset_connection stored procedure to reset connection.
• Enabled cross-database references of objects (tables/views/functions) in views.
• Enabled support of sys.dm_os_sys_info view to provide information about the instance like server_start_time and ms_ticks.
• Enabled support of user connection and network packet size information in sys.configurations view.

High Priority stability enhancements

• Fixed date functions to take into account the timezone setting.
• Improved error handling behavior for 'relation does not exist' and 'column does not exist' errors.
• Fixed sp_tables stored procedure to correctly handle three-part object names across databases to retrieve correct database name during linked servers usage.
• Fixed an issue to enable database owner login to explore database objects in SSMS.
• Fixed sp_tables stored procedure to return correct result when @table_name parameter has square brackets around underscore (_).
• Fixed an issue where index creation could fail if the table is created using SELECT INTO syntax.
• Fixed an permission issue with cross-database function calls.
• Enabled Grant on schema to take effect correctly on future objects created in that schema by any of the schema's authorized users.
• Fixed an issue to pick correct collation for prepared statements.
• Fixed an issue to have foreign key constraint check work correctly when column is created using non-default collation.
• Fixed an issue to insert correct value in the table having identity column.
• Fixed an issue to have correct identity sequence value when bcp / SqlBulkCopy / insert bulk are used with "keep identity values" mode.

Additional improvements and enhancements

• Fixed the issue with Kill command which still left few sessions running after the command.
• Fixed the issue of sys.identity_columns view was wrongly returning more entries than it should.
• Fixed the issue of CASE statement and MIN/MAX functions related to error of string size not being defined or using explicit cast.
• Fixed an issue with ISNUMERIC function to return correct result for nvarchar/varchar parameters.
• Fixed the issue of CASE statement not working correctly when branch expression is of NVARCHAR type.
• Fix behavior of CONCAT() and CONCAT_WS() functions for multibyte characters and to work with atleast 2 and 3 arguments respectively.
• Fixed an issue to allow ALTER COLUMN with type char for Temp Table.
• Fixed an issue in CONVERT function to make it work consistently with BINARY and VARBINARY types in Babelfish.
• Fixed the issue of inconsistent output from select query with FOR XML PATH clause.
• Fixed an issue to rethrow correct TSQL error code.
• Fixed behavior of STRING_AGG() function for input containing multibyte characters.
• Fixed an issue where wrong overloaded variant of regexp_replace is called during restore.
• Fixed cast from sys.varchar to TIME type.
• Enabled use of nvarchar(max) as output parameter in procedure.
• Fixed an issue of missing brackets while declaring the variables in the definition of procedure.

Upgrading to the new version

If you’re running Babelfish in a managed environment, please check your vendor’s instructions for upgrading.MINOR VERSION UPGRADE
Babelfish 4.4.0 is compatible with 4.3.0, 4.2.0, 4.1.0, 4.0.0 versions of Babelfish; a dump/restore is not required to upgrade to the newer version from previous releases. To upgrade:

1. Install the new binaries; for details, see [Compiling Babelfish From Source] (https://babelfishpg.org/docs/installation/compiling-babelfish-from-source/).
2. Start the new server.
3. Update the modified extensions.

To update the extensions that have changed since the last release, connect to the PostgreSQL endpoint of the Babelfish database and execute the following statements from a single session in this order:

ALTER EXTENSION "babelfishpg_common" UPDATE;
ALTER EXTENSION "babelfishpg_tsql" UPDATE;

Please note that the extension owner must invoke ALTER EXTENSION. For this release, the babelfish_tds and babelfish_money extensions don’t include any SQL-level changes and need not be updated.
After the extensions are updated, you can check the extension versions from the PostgreSQL port:

babelfish_db=# \dx
List of installed extensions
Name        | Version |   Schema   |   Description
--------------------+---------+------------+-------------------------------------------------
babelfishpg_common | 4.4.0   | public     | Transact SQL Datatype Support
....
babelfishpg_tsql   | 4.4.0   | public     | Transact SQL Compatibility
....

MAJOR VERSION UPGRADE

Babelfish 4.4.0 provides the ability to upgrade from some 3.x.x and 2.x.x versions of Babelfish. This process is called major version upgrade (MVU). It consists of two steps. First, you need to perform a major version upgrade on the PostgreSQL server to upgrade it to the target version. Second, you need to update all Babelfish extensions to the target version. Currently, Babelfish only supports upgrade using the pg_upgrade module. Other upgrade methods such as dump/restore and replication are not supported.

Overview

Babelfish 3.8.0 contains enhancements and fixes that improve the user experience, including:

New features

• Added support for ALTER FUNCTION syntax.
• Enabled pgaudit support with babelfish.
• Enabled user to create view on stmt with PIVOT operator.
• Enabled support of sys.sp_reset_connection stored proc to reset connection.
• Enabled cross-database references of objects (tables/views/functions) in views.

High Priority stability enhancements

• Fixed date functions to take into account the timezone setting.
• Improved error handling behavior for 'relation does not exist' and 'column does not exist' errors.
• Fixed sp_tables stored procedure to correctly handle three-part object names across databases to retrieve correct database name during linked servers usage.
• Fixed an issue to enable database owner login to explore database objects in SSMS.
• Fixed sp_tables stored procedure to return correct result when @table_name parameter has square brackets around underscore (_).
• Fixed an issue where individual login active directory authentication used to throw error of pg_ad_mapping plugin pointer not initialized.
• Fixed an issue where index creation could fail if the table is created using SELECT INTO syntax.
• Fixed an permission issue with cross-database function calls.
• Enabled Grant on schema to take effect correctly on future objects created in that schema by any of the schema's authorized users.
• Fixed an issue to insert correct value in the table having identity column.
• Fixed an issue to have correct identity sequence value when bcp / SqlBulkCopy / insert bulk are used with "keep identity values" mode.

Additional improvements and enhancements

• Fixed the issue with Kill command which still left few sessions running after the command.
• Fixed the issue of sys.identity_columns view was wrongly returning more entries than it should.
• Fixed the issue of CASE statement and MIN/MAX functions related to error of string size not being defined or using explicit cast.
• Fixed an issue with ISNUMERIC function to return correct result for nvarchar/varchar parameters.
• Fixed the issue of CASE statement not working correctly when branch expression is of NVARCHAR type.
• Fix behavior of CONCAT() and CONCAT_WS() functions for multibyte characters and to work with atleast 2 and 3 arguments respectively.
• Fixed an issue to allow ALTER COLUMN with type char for Temp Table.
• Fixed an issue in CONVERT function to make it work consistently with BINARY and VARBINARY types in Babelfish.
• Fixed the issue of inconsistent output from select query with FOR XML PATH clause.
• Fixed an issue to Rethrow correct TSQL error code.
• Fixed behaviour of STRING_AGG() function for input containing multibyte characters.
• Fixed an issue where wrong overloaded variant of regexp_replace is called during restore.
• Fixed cast from sys.varchar to TIME type.

Upgrading to the new version

If you’re running Babelfish in a managed environment, please check your vendor’s instructions for upgrading.

MINOR VERSION UPGRADE

Babelfish 3.8.0 is compatible with 3.7.0, 3.6.0, 3.5.0, 3.4.0, 3.3.0, 3.2.0 and 3.1.0 versions of Babelfish; a dump/restore is not required to upgrade to the newer version from previous releases. To upgrade:

1. Install the new binaries; for details, see [Compiling Babelfish From Source] (https://babelfishpg.org/docs/installation/compiling-babelfish-from-source/).
2. Start the new server.
3. Update the modified extensions.

To update the extensions that have changed since the last release, connect to the PostgreSQL endpoint of the Babelfish database and execute the following statements from a single session in this order:

ALTER EXTENSION "babelfishpg_common" UPDATE;
ALTER EXTENSION "babelfishpg_tsql" UPDATE;

Please note that the extension owner must invoke ALTER EXTENSION. For this release, the babelfish_tds and babelfish_money extensions don’t include any SQL-level changes and need not be updated.
After the extensions are updated, you can check the extension versions from the PostgreSQL port:

babelfish_db=# \dx
List of installed extensions
Name        | Version |   Schema   |   Description
--------------------+---------+------------+-------------------------------------------------
babelfishpg_common | 3.7.0   | public     | Transact SQL Datatype Support
....
babelfishpg_tsql   | 3.8.0   | public     | Transact SQL Compatibility
....

MAJOR VERSION UPGRADE

Babelfish 3.8.0 provides the ability to upgrade from some 2.x.x versions of Babelfish. This process is called major version upgrade (MVU). It consists of two steps. First, you need to perform a major version upgrade on the PostgreSQL server to upgrade it to the target version. Second, you need to update all Babelfish extensions to the target version. Currently, Babelfish only supports upgrade using the pg_upgrade module. Other upgrade methods such as dump/restore and replication are not supported.

Overview

Babelfish 2.11.0 contains enhancements and fixes that improve the user experience, including:

New features

• Enabled support of sys.sp_reset_connection stored proc to reset connection.

Additional improvements and enhancements

• Fixed an issue with ISNUMERIC function to return correct result for nvarchar/varchar parameters.
• Fixed an issue to rethrow correct TSQL error code.

Upgrading to the new version

If you’re running Babelfish in a managed environment, please check your vendor’s instructions for upgrading.

MINOR VERSION UPGRADE

Babelfish 2.11.0 is compatible with other 2.x.x versions of Babelfish; a dump/restore is not required to upgrade to the newer version from previous releases. To upgrade:

1. Install the new binaries; for details, see [Compiling Babelfish From Source] (https://babelfishpg.org/docs/installation/compiling-babelfish-from-source/).
2. Start the new server.
3. Update the modified extensions.

To update the extensions that have changed since the last release, connect to the PostgreSQL endpoint of the Babelfish database and execute the following statements from a single session in this order:

ALTER EXTENSION "babelfishpg_common" UPDATE;
ALTER EXTENSION "babelfishpg_tsql" UPDATE;

Please note that the extension owner must invoke ALTER EXTENSION. For this release, the babelfish_tds and babelfish_money extensions don’t include any SQL-level changes and need not be updated.
After the extensions are updated, you can check the extension versions from the PostgreSQL port:

babelfish_db=# \dx
List of installed extensions
Name        | Version |   Schema   |   Description
--------------------+---------+------------+-------------------------------------------------
babelfishpg_common | 2.7.0   | public     | Transact SQL Datatype Support
....
babelfishpg_tsql   | 2.11.0   | public     | Transact SQL Compatibility
....

Overview

Babelfish 4.3.0 contains enhancements and fixes that improve the user experience, including:

New features

• Added support for Partitioning in Babelfish.
• Support system functions CHARINDEX(), PATINDEX() and REPLACE() for Babelfish non deterministic collations.
• Added support for STContains, STEquals, STArea PostGIS functions for Geospatial datatypes.

**Security enhancements

**

• Fixed an issue with dropping users and roles by non-privileged user.
• Fixed an issue with sys.database_principals view where it was showing metadata related to all the users irrespective of privileges of server principal.

High Priority stability enhancements

• Fixed an issue with information_schema.tables returning incorrect table_name.
• Fixed an issue where less than operator gives incorrect results for binary data types.
• Fixed inconsistency with OIDs of triggers in OBJECT_ID() function and sys.objects view.
• Fixed an issue where function’s local settings for run-time configuration variables for plpgsql functions may not be reset at the end of function execution when Babelfish is installed.

Additional improvements and enhancements

• Fixed behavior of TRIM(), LTRIM(), RTRIM(), LEFT() and RIGHT() functions for input containing multibyte characters, by returning result with appropriate datatype based on argument datatype.
• Improved performance of like operator with non deterministic collations.
• Fixed an issue where search patch may be incorrect when database name and/or schema name contains special characters in quotes.
• Fixed difference in behavior in Babelfish and TSQL for UPPER() and LOWER() functions with multibyte characters and appropriate argument and return types.
• Added Support of WITH RECOMPILE for Transact-SQL stored procedures and for ALTER PROCEDURE clause.
• The sp_tables procedure now allows you to use the % wildcard character in the @table_qualifier parameter. This makes it easier to search for tables by matching patterns in the table names.
• Fixed difference in behavior in Babelfish and TSQL for STUFF() , SUBSTRING() and TRANSLATE() function with multibyte characters and appropriate argument and return types.
• Updated error message for GRANT/REVOKE/DENY statements.
• Fixed behavior of REVERSE() , REPLACE() and REPLICATE() function for input containing multibyte characters, by returning result with appropriate datatype based on argument datatype.
• Fixed output of SPACE() function for non-positve input argument.
• Improved memory handling when using BCP on a table with indexes. (Contributed by staticlibs)
• Fixed an issue where DML with OUTPUT INTO clause fired a trigger and may result in error.
• Fixed a data type resolve issue with union clause.
• Fixed error message for SUBSTRING() function when the number of arguments are not appropriate.
• Fixed an issue when comment comes with the column name.
• Fixed an issue with sys.server_principals view to show 'public' role entry.
• Added support for WITHIN GROUP clause for STRING_AGG() function.
• Fixed DDL export issue for database with SSMS.
• Added support for new vector extensions like halfvec and sparsevec which got introduced in version 0.7 of pgvector.
• Added support to show windows group membership in T-SQL function IS_MEMBER().

Upgrading to the new version

If you’re running Babelfish in a managed environment, please check your vendor’s instructions for upgrading.MINOR VERSION UPGRADE
Babelfish 4.3.0 is compatible with 4.2.0, 4.1.0, 4.0.0 versions of Babelfish; a dump/restore is not required to upgrade to the newer version from previous releases. To upgrade:

1. Install the new binaries; for details, see [Compiling Babelfish From Source] (https://babelfishpg.org/docs/installation/compiling-babelfish-from-source/).
2. Start the new server.
3. Update the modified extensions.

To update the extensions that have changed since the last release, connect to the PostgreSQL endpoint of the Babelfish database and execute the following statements from a single session in this order:

ALTER EXTENSION "babelfishpg_common" UPDATE;
ALTER EXTENSION "babelfishpg_tsql" UPDATE;

Please note that the extension owner must invoke ALTER EXTENSION. For this release, the babelfish_tds and babelfish_money extensions don’t include any SQL-level changes and need not be updated.
After the extensions are updated, you can check the extension versions from the PostgreSQL port:

babelfish_db=# \dx
List of installed extensions
Name        | Version |   Schema   |   Description
--------------------+---------+------------+-------------------------------------------------
babelfishpg_common | 4.3.0   | public     | Transact SQL Datatype Support
....
babelfishpg_tsql   | 4.3.0   | public     | Transact SQL Compatibility
....

MAJOR VERSION UPGRADE

Babelfish 4.3.0 provides the ability to upgrade from some 3.x.x and 2.x.x versions of Babelfish. This process is called major version upgrade (MVU). It consists of two steps. First, you need to perform a major version upgrade on the PostgreSQL server to upgrade it to the target version. Second, you need to update all Babelfish extensions to the target version. Currently, Babelfish only supports upgrade using the pg_upgrade module. Other upgrade methods such as dump/restore and replication are not supported.