v0.9.0

• Added support for format_datetime function in presto to Databricks (#1250). A new format_datetime function has been added to the Parser class in the presto.py file to provide support for formatting datetime values in Presto on Databricks. This function utilizes the DateFormat.from_arg_list method from the local_expression module to format datetime values according to a specified format string. To ensure compatibility and consistency between Presto and Databricks, a new test file test_format_datetime_1.sql has been added, containing SQL queries that demonstrate the usage of the format_datetime function in Presto and its equivalent in Databricks, DATE_FORMAT. This standalone change adds new functionality without modifying any existing code.
• Added support for SnowFlake SUBSTR (#1238). This commit enhances the library's SnowFlake support by adding the SUBSTR function, which was previously unsupported and existed only as an alternative to SUBSTRING. The project now fully supports both functions, and the SUBSTRING function can be used interchangeably with SUBSTR via the new withConversionStrategy(SynonymOf("SUBSTR")) method. Additionally, this commit supersedes a previous pull request that lacked a GPG signature and includes a test for the SUBSTR function. The ARRAY_SLICE function has also been updated to match SnowFlake's behavior, and the project now supports a more comprehensive list of SQL functions with their corresponding arity.
• Added support for json_size function in presto (#1236). A new json_size function for Presto has been added, which determines the size of a JSON object or array and returns an integer. Two new methods, _build_json_size and get_json_object, have been implemented to handle JSON objects and arrays differently, and the Parser and Tokenizer classes of the Presto class have been updated to include the new json_size function. An alternative implementation for Databricks using SQL functions is provided, and a test case is added to cover a fixed is not null error for json_extract in the Databricks generator. Additionally, a new test file for Presto has been added to test the functionality of the json_extract function in Presto, and a new method GetJsonObject is introduced to extract a JSON object from a given path. The json_extract function has also been updated to extract the value associated with a specified key from JSON data in both Presto and Databricks.
• Enclosed subqueries in parenthesis (#1232). This PR introduces changes to the ExpressionGenerator and LogicalPlanGenerator classes to ensure that subqueries are correctly enclosed in parentheses during code generation. Previously, subqueries were not always enclosed in parentheses, leading to incorrect code. This issue has been addressed by enclosing subqueries in parentheses in the in and scalarSubquery methods, and by adding new match cases for ir.Filter in the LogicalPlanGenerator class. The changes also take care to avoid doubling enclosing parentheses in the .. IN(SELECT...) pattern. New methods have not been added, and existing functionality has been modified to ensure that subqueries are correctly enclosed in parentheses, leading to the generation of correct SQL code. Test cases have been included in a separate PR. These changes improve the correctness of the generated code, avoiding issues such as SELECT * FROM SELECT * FROM t WHERE a > a WHERE a > 'b' and ensuring that the generated code includes parentheses around subqueries.
• Fixed serialization of MultipleErrors (#1177). In the latest release, the encoding of errors in the com.databricks.labs.remorph.coverage package has been improved with an update to the encoders.scala file. The change involves a fix for serializing MultipleErrors instances using the asJson method on each error instead of just the message. This modification ensures that all relevant information about each error is included in the encoded output, improving the accuracy of serialization for MultipleErrors class. Users who handle multiple errors and require precise serialization representation will benefit from this enhancement, as it guarantees comprehensive information encoding for each error instance.
• Fixed presto strpos and array_average functions (#1196). This PR introduces new classes Locate and NamedStruct in the local_expression.py file to handle the STRPOS and ARRAY_AVERAGE functions in a Databricks environment, ensuring compatibility with Presto SQL. The STRPOS function, used to locate the position of a substring within a string, now uses the Locate class and emits a warning regarding differences in implementation between Presto and Databricks SQL. A new method _build_array_average has been added to handle the ARRAY_AVERAGE function in Databricks, which calculates the average of an array, accommodating nulls, integers, and doubles. Two SQL test cases have been added to demonstrate the use of the ARRAY_AVERAGE function with arrays containing integers and doubles. These changes promote compatibility and consistent behavior between Presto and Databricks when dealing with STRPOS and ARRAY_AVERAGE functions, enhancing the ability to migrate between the systems smoothly.
• Handled presto Unnest cross join to Databricks lateral view (#1209). This release introduces new features and updates for handling Presto UNNEST cross joins in Databricks, utilizing the lateral view feature. New methods have been added to improve efficiency and robustness when handling UNNEST cross joins. Additionally, new test cases have been implemented for Presto and Databricks to ensure compatibility and consistency between the two systems in handling UNNEST cross joins, array construction and flattening, and parsing JSON data. Some limitations and issues remain, which will be addressed in future work. The acceptance tests have also been updated, with certain tests now expected to pass, while others may still fail. This release aims to improve the functionality and compatibility of Presto and Databricks when handling UNNEST cross joins and JSON data.
• Implemented remaining TSQL set operations (#1227). This pull request enhances the TSql parser by adding support for parsing and converting the set operations UNION [ALL], EXCEPT, and INTERSECT to the Intermediate Representation (IR). Initially, the grammar recognized these operations, but they were not being converted to the IR. This change resolves issues #1126 and #1102 and includes new unit, transpiler, and functional tests, ensuring the correct behavior of these set operations, including precedence rules. The commit also introduces a new test file, union-all.sql, demonstrating the correct handling of simple UNION ALL operations, ensuring consistent output across TSQL and Databricks SQL platforms.
• Supported multiple columns in order by clause in for ARRAYAGG (#1228). This commit enhances the ARRAYAGG and LISTAGG functions by adding support for multiple columns in the order by clause and sorting in both ascending and descending order. A new method, sortArray, has been introduced to handle multiple sort orders. The changes also improve the functionality of the ARRAYAGG function in the Snowflake dialect by supporting multiple columns in the ORDER BY clause, with an optional DESC keyword for each column. The WithinGroupParams dataclass has been updated in the local expression module to include a list of tuples for the order columns and their sorting direction. These changes provide increased flexibility and control over the output of the ARRAYAGG and LISTAGG functions
• Added TSQL parser support for (LHS) UNION RHS queries (#1211). In this release, we have implemented support for a new form of UNION in the TSQL parser, specifically for queries formatted as (SELECT a from b) UNION [ALL] SELECT x from y. This allows the union of two SELECT queries with an optional ALL keyword to include duplicate rows. The implementation includes a new case statement in the TSqlRelationBuilder class that handles this form of UNION, creating a SetOperation object with the left-hand side and right-hand side of the union, and an is_all flag based on the presence of the ALL keyword. Additionally, we have added support for parsing right-associative UNION clauses in TSQL queries, enhancing the flexibility and expressiveness of the TSQL parser for more complex and nuanced queries. The commit also includes new test cases to verify the correct translation of TSQL set operations to Databricks SQL, resolving issue #1127. This enhancement allows for more accurate parsing of TSQL queries that use the UNION operator in various formats.
• Added support for inline columns in CTEs (#1184). In this release, we have added support for inline columns in Common Table Expressions (CTEs) in Snowflake across various components of our open-source library. This includes updates to the AST (Abstract Syntax Tree) for better TSQL translation and the introduction of the new case class KnownInterval for handling intervals. We have also implemented a new method, DealiasInlineColumnExpressions, in the SnowflakePlanParser class to parse inline columns in CTEs and modify the class constructor to include this new method. Additionally, a new private case class InlineColumnExpression has been introduced to allow for more efficient processing of Snowflake CTEs. The SnowflakeToDatabricksTranspiler has also been updated to support inline columns in CTEs, as demonstrated by a new test case. These changes improve compatibility, precision, and usability of the codebase, providing a better overall experience for software engineers working with CTEs in Snowflake.
• Implemented AST for positional column identifiers (#1181). The recent change introduces an Abstract Syntax Tree (AST) for positional column identifiers in the Snowflake project, specifically in the ExpressionGenerator class. The new NameOrPosition type represents a column identifier, either by name or position. The Id and Position classes inherit from NameOrPosition, and the nameOrPosition method has been added to check and return the appropriate SQL representation. However, due to Databricks' lack of positional column identifier support, the generator side does not yet support this feature. This means that the schema of the table is required to properly translate queries involving positional column identifiers. This enhancement increases the system's flexibility in handling Snowflake's query structures, with the potential for more comprehensive generator-side support in the future.
• Implemented GROUP BY ALL (#1180). The GROUP BY ALL clause is now supported in the LogicalPlanGenerator class of the remorph project, with the addition of a new case to handle the GroupByAll type and updated implementation for the Pivot type. A new case object called GroupByAll has been added to the relations.scala file's sealed trait "GroupType". A new test case has been implemented in the SnowflakeToDatabricksTranspilerTest class to check the correct transpilation of the GROUP BY ALL clause from Snowflake SQL syntax to Databricks SQL syntax. These changes allow for more flexibility and control in grouping operations and enable the implementation of specific functionality for the GROUP BY ALL clause in Snowflake, improving compatibility with Snowflake SQL syntax.

Dependency updates:

• Bump codecov/codecov-action from 4 to 5 (#1210).
• Bump sqlglot from 25.30.0 to 25.32.1 (#1254).

Contributors: @vil1, @asnare, @ericvergnaud, @ganeshdogiparthi-db, @dependabot[bot], @sundarshankar89, @aman-db, @jimidle, @nfx, @bishwajit-db