Repository: pola-rs/polars (language: python)

Issue:
SQL CASE Requires ELSE
### Checks

- [X] I have checked that this issue has not already been reported.
- [X] I have confirmed this bug exists on the [latest version](https://pypi.org/project/polars/) of Polars.

### Reproducible example

```python
SELECT AVG(CASE WHEN A <= B OR A <= C THEN D END) FROM df
```


### Log output

```shell
SQLSyntaxError                            Traceback (most recent call last)
Cell In[19], line 4
      2 ctx.register("results", results)
      3 for sql_query in sql_queries:
----> 4     sql_df = ctx.execute(substitute_results_with_derived_columns(sql_query)).collect()
      5     display(sql_df)
      6     break # for puposes of testing, only run first query.

File c:\Users\bsali\miniconda3\envs\bridge12\Lib\site-packages\polars\sql\context.py:438, in SQLContext.execute(self, query, eager)
    357 def execute(
    358     self, query: str, *, eager: bool | None = None
    359 ) -> LazyFrame | DataFrame:
    360     """
    361     Parse the given SQL query and execute it against the registered frame data.
    362 
   (...)
    436     └────────┴─────────────┴─────────┘
    437     """
--> 438     res = wrap_ldf(self._ctxt.execute(query))
    439     return res.collect() if (eager or self._eager_execution) else res

SQLSyntaxError: ELSE expression is required
```


### Issue description

Is this a bug, non-conformant behavior, or standardized behavior? A Polars SQL statement is calculating the average of values based on a condition. The CASE WHEN doesn't include an ELSE because those values should be ignored. Polars complains that an ELSE is required. If I include an ELSE, with no value, it's a syntax error. The solution is to use ELSE NULL. For comparison, duckdb doesn't required an ELSE. Is ELSE NULL the conforming solution? Or is duckdb giving me a break?

See post on stackoverflow: https://stackoverflow.com/questions/79172783/polars-sql-case

### Expected behavior

No error.

### Installed versions

<details>

```
--------Version info---------
Polars:              1.12.0
Index type:          UInt32
Platform:            Windows-11-10.0.26100-SP0
Python:              3.12.7 | packaged by conda-forge | (main, Oct  4 2024, 15:47:54) [MSC v.1941 64 bit (AMD64)]
LTS CPU:             False

----Optional dependencies----
adbc_driver_manager  <not installed>
altair               4.2.2
cloudpickle          <not installed>
connectorx           <not installed>
deltalake            <not installed>
fastexcel            <not installed>
fsspec               2024.10.0
gevent               <not installed>
great_tables         <not installed>
matplotlib           3.9.2
nest_asyncio         1.6.0
numpy                2.0.2
openpyxl             <not installed>
pandas               2.2.3
pyarrow              17.0.0
pydantic             2.9.2
pyiceberg            <not installed>
sqlalchemy           2.0.35
torch                2.4.1+cpu
xlsx2csv             <not installed>
xlsxwriter           3.2.0```

</details>



Discussion / hints:
> Is this a bug, non-conformant behavior, or standardized behavior? 

By the looks of things it's an oversight - and one I'll fix shortly ;)
Thanks for raising the issue 👌 

Fix patch:
diff --git a/crates/polars-sql/src/sql_expr.rs b/crates/polars-sql/src/sql_expr.rs
index 9e068efb6064..a2ada46e1c68 100644
--- a/crates/polars-sql/src/sql_expr.rs
+++ b/crates/polars-sql/src/sql_expr.rs
@@ -919,7 +919,7 @@ impl SQLExprVisitor<'_> {
             }
             let else_res = match else_result {
                 Some(else_res) => self.visit_expr(else_res)?,
-                None => polars_bail!(SQLSyntax: "ELSE expression is required"),
+                None => lit(Null), // ELSE clause is optional; when omitted, it is implicitly NULL
             };
             if let Some(operand_expr) = operand {
                 let first_operand_expr = self.visit_expr(operand_expr)?;


Test patch:
diff --git a/py-polars/tests/unit/sql/test_conditional.py b/py-polars/tests/unit/sql/test_conditional.py
index b2000ebe37b1..3a80c1234aff 100644
--- a/py-polars/tests/unit/sql/test_conditional.py
+++ b/py-polars/tests/unit/sql/test_conditional.py
@@ -36,6 +36,24 @@ def test_case_when() -> None:
         }
 
 
+@pytest.mark.parametrize("else_clause", ["ELSE NULL ", ""])
+def test_case_when_optional_else(else_clause: str) -> None:
+    df = pl.DataFrame(
+        {
+            "a": [1, 2, 3, 4, 5, 6, 7],
+            "b": [7, 6, 5, 4, 3, 2, 1],
+            "c": [3, 4, 0, 3, 4, 1, 1],
+        }
+    )
+    query = f"""
+        SELECT
+          AVG(CASE WHEN a <= b THEN c {else_clause}END) AS conditional_mean
+          FROM self
+    """
+    res = df.sql(query)
+    assert res.to_dict(as_series=False) == {"conditional_mean": [2.5]}
+
+
 def test_control_flow(foods_ipc_path: Path) -> None:
     nums = pl.LazyFrame(
         {