Windows function not working in 6.X

Follow

Mahalingam Shanmugam

• May 08, 2023 17:32

Team,

In 6.x, throws the following error: "Window expression not supported in this context", when we wrap windows function with case when statement, coalesce statement it is throwing the above error. These queries were working fine in 5.x version. could you please help?

SELECT
  "label_year",
  CASE
    WHEN ("label_year" = 2023) THEN lag("label_YOY-latest") OVER (
      PARTITION BY subquery.dummy
      ORDER BY
        subquery.label_year
    )
    ELSE lag("label_Material_Summary.Net_Price") OVER (
      PARTITION BY subquery.dummy
      ORDER BY
        subquery.label_year
    )
  END AS """lead""" from temp

0

• Official comment

  

  Yoon-Min Nam

  • May 08, 2023 21:32

  Hi Magalingam.
  If you don't mind, can you provide us with more info regarding your problematic query, especially about the `subquery` and the `temp` table?
  I guess you referred to the `subquery which is either another table or a common table expression named `subquery` that the query didn't show.

  Comment actions Permalink
• 

  Mahalingam Shanmugam

  • May 09, 2023 02:22

  Hi Yoon-Min Nam,

  Please find the full query

  SELECT
    "label_year",
    CASE
      WHEN ("label_year" = 2023) THEN lag("label_YOY-latest") OVER (
        PARTITION BY subquery.dummy
        ORDER BY
          subquery.label_year
      )
      ELSE lag("label_Material_Summary.Net_Price") OVER (
        PARTITION BY subquery.dummy
        ORDER BY
          subquery.label_year
      )
    END AS """lead""",
    CASE
      WHEN ("label_year" = 2023) THEN "label_YOY-latest"
      ELSE "label_Material_Summary.Net_Price"
    END AS """lead2"""
  FROM
    (
      SELECT
        round(
          sum(
            coalesce(m_64526f9b_Material_Summary."m_Net_Price", 0)
          ),
          2
        ) AS "label_Material_Summary.Net_Price",
        datepart(
          'year',
          m_64526f9b_Material_Summary."m_PO_Created_on"
        ) AS label_year,
        CAST(
          round(
            sum(
              coalesce(
                CASE
                  WHEN CAST(
                    m_64526f9b_Material_Summary."m_PO_Created_on" AS DATE
                  ) BETWEEN '2023-01-01'
                  AND '2023-05-07' THEN m_64526f9b_Material_Summary."m_Net_Price"
                END,
                0
              )
            ),
            2
          ) AS FLOAT
        ) + CAST(
          round(
            sum(
              coalesce(
                CASE
                  WHEN CAST(
                    m_64526f9b_Material_Summary."m_PO_Created_on" AS DATE
                  ) BETWEEN '2022-01-01'
                  AND '2022-05-07' THEN m_64526f9b_Material_Summary."m_Net_Price"
                END,
                0
              )
            ),
            2
          ) AS FLOAT
        ) AS "label_YOY-latest",
        'dummy' AS dummy
      FROM
        m_64526f9b_Material_Summary
      GROUP BY
        label_year
      ORDER BY
        label_year
  
      LIMIT
        50001
    ) AS subquery
  ORDER BY
    subquery.label_year

  0

  Comment actions Permalink
• 

  Candido Dessanti

  • May 11, 2023 09:32

  Hi Mahalingam Shanmugam,

   

  I tried to reproduce the error with several 6.x releases (6.0, 6.2, and 6.4) both in CPU and GPU mode, but I can't reproduce your error
  
  I used this query on a table with 300M records spawning timestamp of 3 years and using a float field to replace the  m_net_price and a timestamp for m_po_created_on

  select
  label_year,
    CASE
      WHEN ("label_year" = 2014) THEN lag("label_YOY-latest") OVER (
        PARTITION BY subquery.dummy
        ORDER BY
          subquery.label_year
      )
      else 
      lag("label_Material_Summary.Net_Price") OVER (
        PARTITION BY subquery.dummy
        ORDER BY
          subquery.label_year
       )
    end as "lead",
    CASE WHEN ("label_year" = 2015) THEN "label_YOY-latest" else
    "label_Material_Summary.Net_Price"
    end as "lead2"
  from
    (
      select
        round(sum(coalesce(tolls_amount, 0)),2) AS "label_Material_Summary.Net_Price",
        cast(round(sum(coalesce(case when tpep_dropoff_datetime between '2014-01-01' and '2014-07-01' then tip_amount end,0)),2) as float) +
        cast(round(sum(coalesce(case when tpep_dropoff_datetime between '2013-01-01' and '2013-07-01' then tip_amount end,0)),2) as float) as "label_YOY-latest",
        'dummy' as dummy,
        datepart(
          'year',
          yellow_tripdata_p."tpep_dropoff_datetime"
        ) AS label_year
      from
        yellow_tripdata_p
      group by
        label_year
      order by
        label_year
      limit 10
    ) subquery

  Do I forget something in the query?
  
  

   

  0

  Comment actions Permalink
• 

  Candido Dessanti

  • May 11, 2023 14:34

  Hi  Mahalingam Shanmugam,
  
  Yoon-Min Nam identified the issue and he's working on a fix that will land in the 7.x.

  In the meantime as a  workaround, you can try adding a cast to the "label_Material_Summary.Net_Price" expression

  SELECT
        cast(round(
          sum(
            coalesce(m_64526f9b_Material_Summary."m_Net_Price", 0)
          ),
          2
        ) as FLOAT) AS "label_Material_Summary.Net_Price",

   I hope this helps.

  Candido

  0

  Comment actions Permalink
• 

  Mahalingam Shanmugam

  • May 11, 2023 16:52

  Candido Dessanti Thanks. When is the release date for 7.x?

   

  0

  Comment actions Permalink
• 

  Candido Dessanti

  • May 11, 2023 20:46

  We haven't a release date for the version where the fix will be included. The 7.0 is already out, so the fix will be included in 7.0.x or 7.1.

  How about the workaround? Is it working?

  0

  Comment actions Permalink

Please sign in to leave a comment.