Repository: tobymao/sqlglot

Issue:
[Snowflake]: Changed place of 'NOT' in 'WHERE' statement
SQLGlot implicitly changes the place, where 'NOT' is put in 'WHERE' statement. Such change, may result in different output of the query. I would assume that with the same dialect provided on both ends, the same query (logically) would be provided. 

Code to reproduce:
```
from sqlglot import parse_one

if __name__ == '__main__':
    before_sql = "SELECT * FROM table WHERE column NOT IN (1, 2, 3)"
    expression = parse_one(before_sql, dialect='snowflake')
    after_sql = expression.sql(dialect='snowflake')

    assert before_sql == after_sql, f"\n{before_sql}\nIS NOT THE SAME AS\n{after_sql}"
```
Output:
```
AssertionError: 
SELECT * FROM table WHERE column NOT IN (1, 2, 3)
IS NOT THE SAME AS
SELECT * FROM table WHERE NOT column IN (1, 2, 3)
```

Screenshot:
<img width="1143" alt="Zrzut ekranu 2024-08-9 o 13 35 33" src="https://github.com/user-attachments/assets/d45472b0-68c7-4310-8a22-66eb163f1ae2">

Official Snowflake documentation does not cover that case.

Discussion:

Patch:
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index 58fdb620..981dba15 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -406,6 +406,26 @@ class Snowflake(Dialect):
             ),
         }
 
+        def _negate_range(
+            self, this: t.Optional[exp.Expression] = None
+        ) -> t.Optional[exp.Expression]:
+            if not this:
+                return this
+
+            query = this.args.get("query")
+            if isinstance(this, exp.In) and isinstance(query, exp.Query):
+                # Snowflake treats `value NOT IN (subquery)` as `VALUE <> ALL (subquery)`, so
+                # we do this conversion here to avoid parsing it into `NOT value IN (subquery)`
+                # which can produce different results (most likely a SnowFlake bug).
+                #
+                # https://docs.snowflake.com/en/sql-reference/functions/in
+                # Context: https://github.com/tobymao/sqlglot/issues/3890
+                return self.expression(
+                    exp.NEQ, this=this.this, expression=exp.All(this=query.unnest())
+                )
+
+            return self.expression(exp.Not, this=this)
+
         def _parse_with_constraint(self) -> t.Optional[exp.Expression]:
             if self._prev.token_type != TokenType.WITH:
                 self._retreat(self._index - 1)
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 5d69ced5..12b71a67 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -4241,13 +4241,19 @@ class Parser(metaclass=_Parser):
             this = self.expression(exp.Not, this=this)
 
         if negate:
-            this = self.expression(exp.Not, this=this)
+            this = self._negate_range(this)
 
         if self._match(TokenType.IS):
             this = self._parse_is(this)
 
         return this
 
+    def _negate_range(self, this: t.Optional[exp.Expression] = None) -> t.Optional[exp.Expression]:
+        if not this:
+            return this
+
+        return self.expression(exp.Not, this=this)
+
     def _parse_is(self, this: t.Optional[exp.Expression]) -> t.Optional[exp.Expression]:
         index = self._index - 1
         negate = self._match(TokenType.NOT)


Test patch:
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index f62c72b7..e8e86a43 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -138,6 +138,14 @@ WHERE
         self.validate_identity(
             "SELECT * FROM DATA AS DATA_L ASOF JOIN DATA AS DATA_R MATCH_CONDITION (DATA_L.VAL > DATA_R.VAL) ON DATA_L.ID = DATA_R.ID"
         )
+        self.validate_identity(
+            "SELECT * FROM s WHERE c NOT IN (1, 2, 3)",
+            "SELECT * FROM s WHERE NOT c IN (1, 2, 3)",
+        )
+        self.validate_identity(
+            "SELECT * FROM s WHERE c NOT IN (SELECT * FROM t)",
+            "SELECT * FROM s WHERE c <> ALL (SELECT * FROM t)",
+        )
         self.validate_identity(
             "SELECT * FROM t1 INNER JOIN t2 USING (t1.col)",
             "SELECT * FROM t1 INNER JOIN t2 USING (col)",