babelfish-for-postgresql / babelfish_extensions / 14003131597

76%

BABEL_5_X_DEV: 77%

push

github

Support for TSQL UNPIVOT Operator (#3431) (#3610)

This PR implements TSQL UNPIVOT operator support in Babelfish by transforming it into equivalent PostgreSQL CROSS JOIN LATERAL operations. Currently, Babelfish does not support UNPIVOT operations. With this change, users can now use TSQL UNPIVOT syntax to transform wide-format data into long format.

UNPIVOT is a crucial TSQL feature used extensively in data transformation and analytics. Supporting this feature improves compatibility for migrating SQL Server applications to Babelfish.

The implementation adds TSQL UNPIVOT support through a multi-stage transformation approach.

At the parser stage, new grammar rules recognize TSQL UNPIVOT syntax and transform it into PostgreSQL's CROSS JOIN LATERAL structure by creating a JoinExpr node. This transformation also handles source table aliasing (if alias not present), and reassign the unpivot alias as the complete Join alias, returning the transformed node in a list with other metadata (unpivot identifier, unpivot alias, newly produced measure column etc).
At the analyzer stage, the unpivot hook function recursively processes the SelectStmt's fromClause tree to find and unwrap unpivot "List" nodes to make the query tree valid, whilst processing the other meta data from the node to create WHERE IS NOT NULL clause for the dimension column(s) (NULL exclusion (matching SQL Server's behavior)), and to handle SELECT * expansion by filtering out unpivot source columns.

Task: BABEL-4307
Signed-off-by: “manisha-deshpande” <“dmanisha.work@gmail.com”>

80 of 83 new or added lines in 1 file covered. (96.39%)

47612 of 63448 relevant lines covered (75.04%)

193480.58 hits per line