Repository: tobymao/sqlglot

Issue:
Snowflake's DIV0 function is incorrectly translated to a `SELECT CASE` (even when targeting Snowflake as an output dialect)
```py

QUERY = "SELECT DIV0(a, b) FROM t"

# read: None
# write: None
print( sqlglot.parse_one(QUERY).sql() )
# 'SELECT DIV0(a, b) FROM t'

# read: Snowflake
# write: None
print( sqlglot.parse_one(QUERY, read='snowflake').sql() )
# 'SELECT CASE WHEN b = 0 THEN 0 ELSE a / b END FROM t'

# read: None
# write: Snowflake
print( sqlglot.parse_one(QUERY).sql(dialect='snowflake') )
# 'SELECT DIV0(a, b) FROM t'

# read: Snowflake
# write: Snowflake
print( sqlglot.parse_one(QUERY, read='snowflake').sql(dialect='snowflake') )
# 'SELECT IFF(b = 0, 0, a / b) FROM t'
```

When reading the `DIV0(a, b)` function into sqlglot in the `read='snowflake'` dialect, the function is always converted to `IFF(b = 0, 0, a / b)`. However, these functions are different when `a IS NULL` and `b = 0`:

- `DIV0(a, b)` for `(a, b) = (NULL, 0)` gives `NULL`
- `IFF(b = 0, 0, a / b)` for `(a, b) = (NULL, 0)` gives `0`

A correct translation would be more like `IFF(b = 0 AND a IS NOT NULL, 0, a / b)`.

**Official Documentation**
> Please include links to official SQL documentation related to your issue.

Unfortunately, [Snowflake's docs for DIV0](https://docs.snowflake.com/en/sql-reference/functions/div0) don't explicitly indicate the behavior when `a IS NULL`, but I have confirmed the difference in behavior with an actual Snowflake query:

```
SET (a, b) = (NULL, 0);
SELECT DIV0($a, $b), IFF($b = 0, 0, $a/$b);
+--------------+-----------------------+
| DIV0($A, $B) | IFF($B = 0, 0, $A/$B) |
|--------------+-----------------------|
|         NULL |              0.000000 |
+--------------+-----------------------+
```

Snowflake functions typically default to propagating `NULL` inputs through to outputs unless otherwise specified (see e.g. the related function [DIV0NULL](https://docs.snowflake.com/en/sql-reference/functions/div0null) which avoids propagating `NULL`s in the `b` argument).



---

When the Read and Write dialect are the same (as in this case, where our input is Snowflake SQL and our output is also Snowflake SQL) it would be much nicer to preserve the SQL closer to how it was originally written (i.e. retain `DIV0`) - I am not exactly sure how this could be done, possibly using a new sqlglot expression node type like `MultipleImplementations` which has an array of children tagged by dialect, which preserve include the "original" function call expression alongside the dialect-generic implementation?

Alternatively, a flag which disables (some?) function conversions, for the case where the read/write dialects are known to be the same would be useful.

Discussion:

Patch:
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index 9c981aa8..cdb98407 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -93,7 +93,9 @@ def _build_date_time_add(expr_type: t.Type[E]) -> t.Callable[[t.List], E]:
 
 # https://docs.snowflake.com/en/sql-reference/functions/div0
 def _build_if_from_div0(args: t.List) -> exp.If:
-    cond = exp.EQ(this=seq_get(args, 1), expression=exp.Literal.number(0))
+    cond = exp.EQ(this=seq_get(args, 1), expression=exp.Literal.number(0)).and_(
+        exp.Is(this=seq_get(args, 0), expression=exp.null()).not_()
+    )
     true = exp.Literal.number(0)
     false = exp.Div(this=seq_get(args, 0), expression=seq_get(args, 1))
     return exp.If(this=cond, true=true, false=false)


Test patch:
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index d01101e5..7837cc9e 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -598,12 +598,12 @@ WHERE
         self.validate_all(
             "DIV0(foo, bar)",
             write={
-                "snowflake": "IFF(bar = 0, 0, foo / bar)",
-                "sqlite": "IIF(bar = 0, 0, CAST(foo AS REAL) / bar)",
-                "presto": "IF(bar = 0, 0, CAST(foo AS DOUBLE) / bar)",
-                "spark": "IF(bar = 0, 0, foo / bar)",
-                "hive": "IF(bar = 0, 0, foo / bar)",
-                "duckdb": "CASE WHEN bar = 0 THEN 0 ELSE foo / bar END",
+                "snowflake": "IFF(bar = 0 AND NOT foo IS NULL, 0, foo / bar)",
+                "sqlite": "IIF(bar = 0 AND NOT foo IS NULL, 0, CAST(foo AS REAL) / bar)",
+                "presto": "IF(bar = 0 AND NOT foo IS NULL, 0, CAST(foo AS DOUBLE) / bar)",
+                "spark": "IF(bar = 0 AND NOT foo IS NULL, 0, foo / bar)",
+                "hive": "IF(bar = 0 AND NOT foo IS NULL, 0, foo / bar)",
+                "duckdb": "CASE WHEN bar = 0 AND NOT foo IS NULL THEN 0 ELSE foo / bar END",
             },
         )
         self.validate_all(