Repository: tobymao/sqlglot

Issue:
IS NOT NULL incorrect transformation
Wrong `IS NOT NULL` transormation

```python
from sqlglot import Dialects, parse_one

sql = "SELECT * FROM ABC WHERE toUInt8OrNull(num) IS NOT NULL"

print(parse_one(sql, read=Dialects.CLICKHOUSE).sql(dialect=Dialects.CLICKHOUSE))
```

Output with the version SQLGlot v25.27.0

```sql
SELECT * FROM ABC WHERE NOT toUInt8OrNull(num) IS NULL
```

Simmilar to https://github.com/tobymao/sqlglot/issues/4285

Hints from issue comments:
@georgesittas: Can you please elaborate on what's wrong? I just ran this against a CH client and there's no syntax error being reported.

Make sure to create more detailed tickets in the future.@georgesittas: Seems like it's working fine:

```
george :) WITH ABC AS (SELECT '1' AS num UNION ALL SELECT NULL AS num) SELECT * FROM ABC WHERE toUInt8OrNull(num) IS NOT NULL;

WITH ABC AS
    (
        SELECT '1' AS num
        UNION ALL
        SELECT NULL AS num
    )
SELECT *
FROM ABC
WHERE toUInt8OrNull(num) IS NOT NULL

┌─num─┐
│ 1   │
└─────┘

1 row in set. Elapsed: 0.012 sec.

george :) WITH ABC AS (SELECT '1' AS num UNION ALL SELECT NULL AS num) SELECT * FROM ABC WHERE NOT toUInt8OrNull(num) IS NULL;

WITH ABC AS
    (
        SELECT '1' AS num
        UNION ALL
        SELECT NULL AS num
    )
SELECT *
FROM ABC
WHERE NOT (toUInt8OrNull(num) IS NULL)

┌─num─┐
│ 1   │
└─────┘

1 row in set. Elapsed: 0.004 sec.
```@elchyn-cheliabiyeu: So the issue reproduced in more complex condition, since sqlglot does not add braces like in your example `WHERE NOT (toUInt8OrNull(num) IS NULL)` this leads to wrong operator execution order

```sql
SELECT (toUInt8('1') + toUInt8('2')) IS NOT NULL
```
 -> True

```sql
SELECT NOT (toUInt8('1') + toUInt8('2')) IS NULL
```
 -> False

```python
from sqlglot import Dialects, parse_one

sql = "SELECT (toUInt8('1') + toUInt8('2')) IS NOT NULL"

print(parse_one(sql, read=Dialects.CLICKHOUSE).sql(dialect=Dialects.CLICKHOUSE))
```

Returned value 
```sql
SELECT NOT (toUInt8('1') + toUInt8('2')) IS NULL
```

Should be
```sql
SELECT NOT ((toUInt8('1') + toUInt8('2')) IS NULL)
```
SQLGlot v25.27.0
ClickHouse (ver. 24.8.5.115)
@georgesittas: I see, thanks for clarifying, will fix.

Solution patch:
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index fa86e3ac66..25cc399d86 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -1223,8 +1223,8 @@ def projectiondef_sql(self, expression: exp.ProjectionDef) -> str:
         def is_sql(self, expression: exp.Is) -> str:
             is_sql = super().is_sql(expression)
 
-            if isinstance(expression.parent, exp.Not) and isinstance(expression.this, exp.Subquery):
-                # WHERE (SELECT ...) IS NOT NULL -> NOT ((SELECT ...) IS NULL)
+            if isinstance(expression.parent, exp.Not):
+                # value IS NOT NULL -> NOT (value IS NULL)
                 is_sql = self.wrap(is_sql)
 
             return is_sql


Test patch:
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index b3a2ddcf50..a0efb54eb7 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -153,6 +153,10 @@ def test_clickhouse(self):
         self.validate_identity(
             "CREATE TABLE t (foo String CODEC(LZ4HC(9), ZSTD, DELTA), size String ALIAS formatReadableSize(size_bytes), INDEX idx1 a TYPE bloom_filter(0.001) GRANULARITY 1, INDEX idx2 a TYPE set(100) GRANULARITY 2, INDEX idx3 a TYPE minmax GRANULARITY 3)"
         )
+        self.validate_identity(
+            "SELECT (toUInt8('1') + toUInt8('2')) IS NOT NULL",
+            "SELECT NOT ((toUInt8('1') + toUInt8('2')) IS NULL)",
+        )
         self.validate_identity(
             "SELECT $1$foo$1$",
             "SELECT 'foo'",
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index 43644a0e41..85402e2cd3 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -526,7 +526,7 @@ def test_nvl2(self):
             write={
                 "": "SELECT NVL2(a, b, c)",
                 "bigquery": "SELECT CASE WHEN NOT a IS NULL THEN b ELSE c END",
-                "clickhouse": "SELECT CASE WHEN NOT a IS NULL THEN b ELSE c END",
+                "clickhouse": "SELECT CASE WHEN NOT (a IS NULL) THEN b ELSE c END",
                 "databricks": "SELECT NVL2(a, b, c)",
                 "doris": "SELECT CASE WHEN NOT a IS NULL THEN b ELSE c END",
                 "drill": "SELECT CASE WHEN NOT a IS NULL THEN b ELSE c END",
@@ -552,7 +552,7 @@ def test_nvl2(self):
             write={
                 "": "SELECT NVL2(a, b)",
                 "bigquery": "SELECT CASE WHEN NOT a IS NULL THEN b END",
-                "clickhouse": "SELECT CASE WHEN NOT a IS NULL THEN b END",
+                "clickhouse": "SELECT CASE WHEN NOT (a IS NULL) THEN b END",
                 "databricks": "SELECT NVL2(a, b)",
                 "doris": "SELECT CASE WHEN NOT a IS NULL THEN b END",
                 "drill": "SELECT CASE WHEN NOT a IS NULL THEN b END",