Repository: tobymao/sqlglot

Issue:
escaped `\0` characters in clickhouse are interpreted as null bytes
**Fully reproducible code snippet**

```
In [17]: import sqlglot as sg

In [18]: expr = sg.parse_one("select '\\0'").sql("clickhouse")

In [19]: expr
Out[19]: "SELECT '\\0'"

In [20]: print(expr)
SELECT '\0'

In [21]: !clickhouse local <<< "$expr"
\0

In [22]: !clickhouse local <<< "SELECT ASCII('\0')"
0

In [23]: !clickhouse local <<< "SELECT ASCII('\1')"
92

In [24]: !clickhouse local <<< "SELECT ASCII('\x01')"
1
```

I think because `\\0` in Python means "the character `\` followed by the character `0`" and not "the null byte", sqlglot should backslash-escape `\0` strings.

Note that it seems to be specific to `\0` and not other backslashed characters:

```
In [29]: !clickhouse local <<< "SELECT '\0'"
\0

In [30]: !clickhouse local <<< "SELECT '\1'"
\\1
```

**Official Documentation**

https://clickhouse.com/docs/en/sql-reference/syntax#string


Discussion:

Patch:
diff --git a/setup.py b/setup.py
index 496d0b19..fa682775 100644
--- a/setup.py
+++ b/setup.py
@@ -16,7 +16,7 @@ setup(
         "fallback_version": "0.0.0",
         "local_scheme": "no-local-version",
     },
-    setup_requires=["setuptools_scm<8.0.1"],
+    setup_requires=["setuptools_scm"],
     python_requires=">=3.7",
     extras_require={
         "dev": [
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index 69a7c6b3..0d741b5b 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -190,6 +190,16 @@ class BigQuery(Dialect):
         "%D": "%m/%d/%y",
     }
 
+    ESCAPE_SEQUENCES = {
+        "\\a": "\a",
+        "\\b": "\b",
+        "\\f": "\f",
+        "\\n": "\n",
+        "\\r": "\r",
+        "\\t": "\t",
+        "\\v": "\v",
+    }
+
     FORMAT_MAPPING = {
         "DD": "%d",
         "MM": "%m",
@@ -416,7 +426,6 @@ class BigQuery(Dialect):
         TABLE_HINTS = False
         LIMIT_FETCH = "LIMIT"
         RENAME_TABLE_WITH_DB = False
-        ESCAPE_LINE_BREAK = True
         NVL2_SUPPORTED = False
         UNNEST_WITH_ORDINALITY = False
         COLLATE_IS_FUNC = True
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index 5ee13416..e9d93266 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -37,6 +37,10 @@ class ClickHouse(Dialect):
     STRICT_STRING_CONCAT = True
     SUPPORTS_USER_DEFINED_TYPES = False
 
+    ESCAPE_SEQUENCES = {
+        "\\0": "\0",
+    }
+
     class Tokenizer(tokens.Tokenizer):
         COMMENTS = ["--", "#", "#!", ("/*", "*/")]
         IDENTIFIERS = ['"', "`"]
diff --git a/sqlglot/dialects/dialect.py b/sqlglot/dialects/dialect.py
index 3650ff5c..bd839afc 100644
--- a/sqlglot/dialects/dialect.py
+++ b/sqlglot/dialects/dialect.py
@@ -81,6 +81,8 @@ class _Dialect(type):
         klass.INVERSE_TIME_MAPPING = {v: k for k, v in klass.TIME_MAPPING.items()}
         klass.INVERSE_TIME_TRIE = new_trie(klass.INVERSE_TIME_MAPPING)
 
+        klass.INVERSE_ESCAPE_SEQUENCES = {v: k for k, v in klass.ESCAPE_SEQUENCES.items()}
+
         klass.tokenizer_class = getattr(klass, "Tokenizer", Tokenizer)
         klass.parser_class = getattr(klass, "Parser", Parser)
         klass.generator_class = getattr(klass, "Generator", Generator)
@@ -188,6 +190,9 @@ class Dialect(metaclass=_Dialect):
     # special syntax cast(x as date format 'yyyy') defaults to time_mapping
     FORMAT_MAPPING: t.Dict[str, str] = {}
 
+    # Mapping of an unescaped escape sequence to the corresponding character
+    ESCAPE_SEQUENCES: t.Dict[str, str] = {}
+
     # Columns that are auto-generated by the engine corresponding to this dialect
     # Such columns may be excluded from SELECT * queries, for example
     PSEUDOCOLUMNS: t.Set[str] = set()
@@ -204,6 +209,8 @@ class Dialect(metaclass=_Dialect):
     INVERSE_TIME_MAPPING: t.Dict[str, str] = {}
     INVERSE_TIME_TRIE: t.Dict = {}
 
+    INVERSE_ESCAPE_SEQUENCES: t.Dict[str, str] = {}
+
     def __eq__(self, other: t.Any) -> bool:
         return type(self) == other
 
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 1ed56a81..4a02fe8c 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -346,6 +346,7 @@ class Generator:
     # Autofilled
     INVERSE_TIME_MAPPING: t.Dict[str, str] = {}
     INVERSE_TIME_TRIE: t.Dict = {}
+    INVERSE_ESCAPE_SEQUENCES: t.Dict[str, str] = {}
     INDEX_OFFSET = 0
     UNNEST_COLUMN_ONLY = False
     ALIAS_POST_TABLESAMPLE = False
@@ -353,7 +354,6 @@ class Generator:
     STRICT_STRING_CONCAT = False
     NORMALIZE_FUNCTIONS: bool | str = "upper"
     NULL_ORDERING = "nulls_are_small"
-    ESCAPE_LINE_BREAK = False
 
     can_identify: t.Callable[[str, str | bool], bool]
 
@@ -1670,8 +1670,8 @@ class Generator:
 
     def escape_str(self, text: str) -> str:
         text = text.replace(self.QUOTE_END, self._escaped_quote_end)
-        if self.ESCAPE_LINE_BREAK:
-            text = text.replace("\n", "\\n")
+        if self.INVERSE_ESCAPE_SEQUENCES:
+            text = "".join(self.INVERSE_ESCAPE_SEQUENCES.get(ch, ch) for ch in text)
         elif self.pretty:
             text = text.replace("\n", self.SENTINEL_LINE_BREAK)
         return text
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index 71a3f8e8..080a86bc 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -493,6 +493,7 @@ class Tokenizer(metaclass=_Tokenizer):
     QUOTES: t.List[t.Tuple[str, str] | str] = ["'"]
     STRING_ESCAPES = ["'"]
     VAR_SINGLE_TOKENS: t.Set[str] = set()
+    ESCAPE_SEQUENCES: t.Dict[str, str] = {}
 
     # Autofilled
     IDENTIFIERS_CAN_START_WITH_DIGIT: bool = False
@@ -1203,6 +1204,13 @@ class Tokenizer(metaclass=_Tokenizer):
                 if self._end:
                     raise TokenError(f"Missing {delimiter} from {self._line}:{self._start}")
 
+                if self.ESCAPE_SEQUENCES and self._peek and self._char in self.STRING_ESCAPES:
+                    escaped_sequence = self.ESCAPE_SEQUENCES.get(self._char + self._peek)
+                    if escaped_sequence:
+                        self._advance(2)
+                        text += escaped_sequence
+                        continue
+
                 current = self._current - 1
                 self._advance(alnum=True)
                 text += self.sql[current : self._current - 1]


Test patch:
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 07bef668..85218786 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -138,6 +138,16 @@ class TestBigQuery(Validator):
         self.validate_all('x <> """"""', write={"bigquery": "x <> ''"})
         self.validate_all("x <> ''''''", write={"bigquery": "x <> ''"})
         self.validate_all("CAST(x AS DATETIME)", read={"": "x::timestamp"})
+        self.validate_all(
+            "SELECT '\\n'",
+            read={
+                "bigquery": "SELECT '''\n'''",
+            },
+            write={
+                "bigquery": "SELECT '\\n'",
+                "postgres": "SELECT '\n'",
+            },
+        )
         self.validate_all(
             "TRIM(item, '*')",
             read={
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index cddfe424..948c00e1 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -85,6 +85,16 @@ class TestClickhouse(Validator):
             "CREATE MATERIALIZED VIEW test_view (id UInt8) TO db.table1 AS SELECT * FROM test_data"
         )
 
+        self.validate_all(
+            "SELECT '\\0'",
+            read={
+                "mysql": "SELECT '\0'",
+            },
+            write={
+                "clickhouse": "SELECT '\\0'",
+                "mysql": "SELECT '\0'",
+            },
+        )
         self.validate_all(
             "DATE_ADD('day', 1, x)",
             read={