@SAFE_SUB gets optimized during model rendering, causing improper calculations

[jukiewiczm]

SQLMesh version: 0.228.1
Gateways: DuckDB, MotherDuck

I actually suspect it might be an SQLGlot issue, but I did experience it in SQLMesh, so starting there.

Consider this model:

MODEL (
  name test.test,
  owner mjukiewicz,
  kind FULL,
  audits (
    NOT_NULL(columns := (
      final_price
    ))
  )
);

with test_data as (
    select 100 as price, NULL as amount_off, 20 as percent_off, 1 as one
    UNION
    select 200 as price, 25 as amount_off, NULL as percent_off, 1 as one
    UNION
    select 500 as price, 10 as amount_off, 100 as percent_off, 1 as one
)
select
    --parentheses don't change anything by the way
    (@SAFE_SUB(price, amount_off)) * (@SAFE_SUB(1, percent_off / 100)) as final_price
from test_data

(btw I realize the usage of SAFE_SUB might not be the most reasonable choice here, but I believe it should still work just fine)

What is supposed to happen in this calculation is pretty clear: (price - amount_off) * (1 - (percent_off / 100)).

However, what this model gets rendered to is:

WITH "test_data" AS (                                                                                                                                                                                                 
  SELECT                                                                                                                                                                                                              
    100 AS "price",                                                                                                                                                                                                   
    NULL AS "amount_off",                                                                                                                                                                                             
    20 AS "percent_off",                                                                                                                                                                                              
    1 AS "one"                                                                                                                                                                                                        
  UNION                                                                                                                                                                                                               
  SELECT                                                                                                                                                                                                              
    200 AS "price",                                                                                                                                                                                                   
    25 AS "amount_off",                                                                                                                                                                                               
    NULL AS "percent_off",                                                                                                                                                                                            
    1 AS "one"                                                                                                                                                                                                        
  UNION                                                                                                                                                                                                               
  SELECT                                                                                                                                                                                                              
    500 AS "price",                                                                                                                                                                                                   
    10 AS "amount_off",                                                                                                                                                                                               
    100 AS "percent_off",                                                                                                                                                                                             
    1 AS "one"                                                                                                                                                                                                        
)                                                                                                                                                                                                                     
SELECT                                                                                                                                                                                                                
  CASE                                                                                                                                                                                                                
    WHEN "test_data"."amount_off" IS NULL AND "test_data"."price" IS NULL                                                                                                                                             
    THEN NULL                                                                                                                                                                                                         
    ELSE COALESCE("test_data"."price", 0) - COALESCE("test_data"."amount_off", 0)                                                                                                                                     
  END * COALESCE(1, 0) - COALESCE("test_data"."percent_off" / 100, 0) AS "final_price" -- it's wrong right here                                                                                                                               
FROM "test_data" AS "test_data" 

This is an equivalent of (price - amount_off) * 1 - (percent_off / 100), which is obviously wrong.

What happens is that when SQLMesh/SQLGlot sees the expanded macro with CASE WHEN 1 IS NULL AND ... it optimizes it by writing the ELSE case directly instead. This is proved by the following examples:

This version works correctly, as it creates the CASE statement for the right side of the calculation (as there is no numeric literal):
(@SAFE_SUB(price, amount_off)) * (@SAFE_SUB(one, percent_off / 100)) as final_price

This version works incorrectly, as it's the direct expansion of the SAFE_SUB macro:
(@SAFE_SUB(price, amount_off)) * (CASE WHEN 1 IS NULL AND percent_off / 100 IS NULL THEN NULL ELSE COALESCE(1, 0) - COALESCE(percent_off / 100, 0) END) as final_price

This would be OK in general if the optimized part was parenthesized, but it's not. And additionally, even explicit parentheses are being ignored in this case (I can imagine why, but I don't want to make any guesses).

Side note:

I also experienced this getting rendered properly when using sqlmesh render ... on a not-yet existing model, then being calculated improperly during sqlmesh plan, and then getting the wrong rendered version again when running sqlmesh render after sqlmesh plan, though I couldn't easily reproduce this phenomenon.