[BUG]: lateral column alias in group by clause not supported

pamons-databricks commented 1 month ago

Is there an existing issue for this?

[X] I have searched the existing issues

Category of Bug / Issue

TranspileLateralColumnAliasError

Current Behavior

If there is a lateral column alias referenced in a aggregate function the validation will fail since that is not supported Source dialect is Snowflake

Expected Behavior

Lateral aliases in a aggregate function should be replaced

Steps To Reproduce

Running transpile on the example query with validation

Relevant log output or Exception details

[UNSUPPORTED_FEATURE.LATERAL_COLUMN_ALIAS_IN_AGGREGATE_FUNC] The feature is not supported: Referencing a lateral column alias `reporting_date` in the aggregate function "sum(CASE WHEN (months_between(lateralAliasReference(reporting_date), reporting_start_date, true) < 12) THEN ((retained_val * 0.3) / 12.0) ELSE 0 END)". SQLSTATE: 0A000

Sample Query

SELECT
    LAST_DAY(current_date) AS reporting_date,
    SUM(
      CASE
        WHEN MONTHS_BETWEEN(reporting_date, reporting_start_date) < 12
            THEN (retained_val * 0.3) / 12.0
        ELSE 0
      END
    ) AS app_store_fees
FROM test_tbl

Operating System

macOS

Version

latest via Databricks CLI

jimidle commented 1 month ago

We need to add the source dialect to these bug reports.

pamons-databricks commented 4 weeks ago

The source dialect is Snowflake

databrickslabs / remorph