Repository: tobymao/sqlglot

Issue:
Incorrect binary conversion for duckdb
When converting statements from snowflake to duckdb (via [fakesnow](https://github.com/tekumara/fakesnow)) we get casting errors, because the result is not castable to a blob.

```
duckdb.duckdb.ConversionException: Conversion Error: Unimplemented type for cast (HUGEINT -> BLOB)
```

**Fully reproducible code snippet**
The code below will reproduce the issue:

```python
>>> import sqlglot
>>> e = sqlglot.parse_one("SELECT x'abcdef'", read="snowflake")
>>> e.expressions[0].sql("duckdb")
'11259375'
>>> e.sql("duckdb")
'SELECT 11259375'
```

**Official Documentation**
However, note that duckdb supports binary [literals](https://duckdb.org/docs/sql/data_types/blob.html). 

I tested the following query on a duckdb [playground](https://codapi.org/embed/?sandbox=duckdb&code=data%3A%3Bbase64%2CC3b1cXUOUVCPqUhMiqlITompSE1Tt7JKyslP0lFIK8rPjc9IrdBQT0xKTklNU9fUwapSwRaLUgA%3D):
```sql
SELECT '\xab\xcd\xef'::blob, from_hex('abcdef'), '\xab\xcd\xef'::blob = from_hex('abcdef')

│ '\xAB\xCD\xEF'::BLOB │ from_hex('abcdef') │ ('\xAB\xCD\xEF'::BLOB = from_hex('abcdef')) │
├──────────────────────┼────────────────────┼─────────────────────────────────────────────┤
│ \xAB\xCD\xEF         │ \xAB\xCD\xEF       │ true                                        │
```

Discussion:

Solution patch:
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index 1b49f837bc..09af7db0d9 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -356,6 +356,7 @@ class BigQuery(Dialect):
     FORCE_EARLY_ALIAS_REF_EXPANSION = True
     EXPAND_ALIAS_REFS_EARLY_ONLY_IN_GROUP_BY = True
     PRESERVE_ORIGINAL_NAMES = True
+    HEX_STRING_IS_INTEGER_TYPE = True
 
     # https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#case_sensitivity
     NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_INSENSITIVE
@@ -903,6 +904,7 @@ class Generator(generator.Generator):
             exp.GenerateSeries: rename_func("GENERATE_ARRAY"),
             exp.GroupConcat: rename_func("STRING_AGG"),
             exp.Hex: lambda self, e: self.func("UPPER", self.func("TO_HEX", self.sql(e, "this"))),
+            exp.HexString: lambda self, e: self.hexstring_sql(e, binary_function_repr="FROM_HEX"),
             exp.If: if_sql(false_value="NULL"),
             exp.ILike: no_ilike_sql,
             exp.IntDiv: rename_func("DIV"),
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index c2cf3d2568..7237813ae8 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -190,6 +190,7 @@ class ClickHouse(Dialect):
     PRESERVE_ORIGINAL_NAMES = True
     NUMBERS_CAN_BE_UNDERSCORE_SEPARATED = True
     IDENTIFIERS_CAN_START_WITH_DIGIT = True
+    HEX_STRING_IS_INTEGER_TYPE = True
 
     # https://github.com/ClickHouse/ClickHouse/issues/33935#issue-1112165779
     NORMALIZATION_STRATEGY = NormalizationStrategy.CASE_SENSITIVE
@@ -1042,7 +1043,7 @@ class Generator(generator.Generator):
             exp.SHA2: sha256_sql,
             exp.UnixToTime: _unix_to_time_sql,
             exp.TimestampTrunc: timestamptrunc_sql(zone=True),
-            exp.Trim: trim_sql,
+            exp.Trim: lambda self, e: trim_sql(self, e, default_trim_type="BOTH"),
             exp.Variance: rename_func("varSamp"),
             exp.SchemaCommentProperty: lambda self, e: self.naked_property(e),
             exp.Stddev: rename_func("stddevSamp"),
@@ -1098,7 +1099,7 @@ def strtodate_sql(self, expression: exp.StrToDate) -> str:
             if not isinstance(expression.parent, exp.Cast):
                 # StrToDate returns DATEs in other dialects (eg. postgres), so
                 # this branch aims to improve the transpilation to clickhouse
-                return f"CAST({strtodate_sql} AS DATE)"
+                return self.cast_sql(exp.cast(expression, "DATE"))
 
             return strtodate_sql
 
diff --git a/sqlglot/dialects/dialect.py b/sqlglot/dialects/dialect.py
index a1ad4bfbb2..433c23ec5e 100644
--- a/sqlglot/dialects/dialect.py
+++ b/sqlglot/dialects/dialect.py
@@ -469,6 +469,9 @@ class Dialect(metaclass=_Dialect):
     NUMBERS_CAN_BE_UNDERSCORE_SEPARATED = False
     """Whether number literals can include underscores for better readability"""
 
+    HEX_STRING_IS_INTEGER_TYPE: bool = False
+    """Whether hex strings such as x'CC' evaluate to integer or binary/blob type"""
+
     REGEXP_EXTRACT_DEFAULT_GROUP = 0
     """The default value for the capturing group."""
 
@@ -1404,9 +1407,9 @@ def count_if_to_sum(self: Generator, expression: exp.CountIf) -> str:
     return self.func("sum", exp.func("if", cond, 1, 0))
 
 
-def trim_sql(self: Generator, expression: exp.Trim) -> str:
+def trim_sql(self: Generator, expression: exp.Trim, default_trim_type: str = "") -> str:
     target = self.sql(expression, "this")
-    trim_type = self.sql(expression, "position")
+    trim_type = self.sql(expression, "position") or default_trim_type
     remove_chars = self.sql(expression, "expression")
     collation = self.sql(expression, "collation")
 
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index 7d7a671014..fef779951c 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -580,6 +580,7 @@ class Generator(generator.Generator):
             exp.Encode: lambda self, e: encode_decode_sql(self, e, "ENCODE", replace=False),
             exp.GenerateDateArray: _generate_datetime_array_sql,
             exp.GenerateTimestampArray: _generate_datetime_array_sql,
+            exp.HexString: lambda self, e: self.hexstring_sql(e, binary_function_repr="FROM_HEX"),
             exp.Explode: rename_func("UNNEST"),
             exp.IntDiv: lambda self, e: self.binary(e, "//"),
             exp.IsInf: rename_func("ISINF"),
diff --git a/sqlglot/dialects/presto.py b/sqlglot/dialects/presto.py
index 9e42c4c53e..eea7ac0b65 100644
--- a/sqlglot/dialects/presto.py
+++ b/sqlglot/dialects/presto.py
@@ -222,6 +222,7 @@ class Presto(Dialect):
     }
 
     class Tokenizer(tokens.Tokenizer):
+        HEX_STRINGS = [("x'", "'"), ("X'", "'")]
         UNICODE_STRINGS = [
             (prefix + q, q)
             for q in t.cast(t.List[str], tokens.Tokenizer.QUOTES)
diff --git a/sqlglot/dialects/trino.py b/sqlglot/dialects/trino.py
index 5f95ae29b8..b8bb8c0a61 100644
--- a/sqlglot/dialects/trino.py
+++ b/sqlglot/dialects/trino.py
@@ -11,9 +11,6 @@ class Trino(Presto):
     SUPPORTS_USER_DEFINED_TYPES = False
     LOG_BASE_FIRST = True
 
-    class Tokenizer(Presto.Tokenizer):
-        HEX_STRINGS = [("X'", "'")]
-
     class Parser(Presto.Parser):
         FUNCTION_PARSERS = {
             **Presto.Parser.FUNCTION_PARSERS,
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index b84dbd814b..4f02d0700d 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -1608,7 +1608,7 @@ class BitString(Condition):
 
 
 class HexString(Condition):
-    pass
+    arg_types = {"this": True, "is_integer": False}
 
 
 class ByteString(Condition):
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index a9b1a54e3f..44834b71e0 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -1276,11 +1276,30 @@ def bitstring_sql(self, expression: exp.BitString) -> str:
             return f"{self.dialect.BIT_START}{this}{self.dialect.BIT_END}"
         return f"{int(this, 2)}"
 
-    def hexstring_sql(self, expression: exp.HexString) -> str:
+    def hexstring_sql(
+        self, expression: exp.HexString, binary_function_repr: t.Optional[str] = None
+    ) -> str:
         this = self.sql(expression, "this")
-        if self.dialect.HEX_START:
-            return f"{self.dialect.HEX_START}{this}{self.dialect.HEX_END}"
-        return f"{int(this, 16)}"
+        is_integer_type = expression.args.get("is_integer")
+
+        if (is_integer_type and not self.dialect.HEX_STRING_IS_INTEGER_TYPE) or (
+            not self.dialect.HEX_START and not binary_function_repr
+        ):
+            # Integer representation will be returned if:
+            # - The read dialect treats the hex value as integer literal but not the write
+            # - The transpilation is not supported (write dialect hasn't set HEX_START or the param flag)
+            return f"{int(this, 16)}"
+
+        if not is_integer_type:
+            # Read dialect treats the hex value as BINARY/BLOB
+            if binary_function_repr:
+                # The write dialect supports the transpilation to its equivalent BINARY/BLOB
+                return self.func(binary_function_repr, exp.Literal.string(this))
+            if self.dialect.HEX_STRING_IS_INTEGER_TYPE:
+                # The write dialect does not support the transpilation, it'll treat the hex value as INTEGER
+                self.unsupported("Unsupported transpilation from BINARY/BLOB hex string")
+
+        return f"{self.dialect.HEX_START}{this}{self.dialect.HEX_END}"
 
     def bytestring_sql(self, expression: exp.ByteString) -> str:
         this = self.sql(expression, "this")
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index d527a64ac9..2c65ea6721 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -867,7 +867,11 @@ class Parser(metaclass=_Parser):
     NUMERIC_PARSERS = {
         TokenType.BIT_STRING: lambda self, token: self.expression(exp.BitString, this=token.text),
         TokenType.BYTE_STRING: lambda self, token: self.expression(exp.ByteString, this=token.text),
-        TokenType.HEX_STRING: lambda self, token: self.expression(exp.HexString, this=token.text),
+        TokenType.HEX_STRING: lambda self, token: self.expression(
+            exp.HexString,
+            this=token.text,
+            is_integer=self.dialect.HEX_STRING_IS_INTEGER_TYPE or None,
+        ),
         TokenType.NUMBER: lambda self, token: self.expression(
             exp.Literal, this=token.text, is_string=False
         ),


Test patch:
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index e99a2e9d3c..44221c48af 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -186,6 +186,13 @@ def test_clickhouse(self):
             "SELECT SUM(1) AS impressions FROM (SELECT ['Istanbul', 'Berlin', 'Bobruisk'] AS cities) WHERE arrayJoin(cities) IN ('Istanbul', 'Berlin')",
         )
 
+        self.validate_all(
+            "SELECT CAST(STR_TO_DATE(SUBSTRING(a.eta, 1, 10), '%Y-%m-%d') AS Nullable(DATE))",
+            read={
+                "clickhouse": "SELECT CAST(STR_TO_DATE(SUBSTRING(a.eta, 1, 10), '%Y-%m-%d') AS Nullable(DATE))",
+                "oracle": "SELECT to_date(substr(a.eta, 1,10), 'YYYY-MM-DD')",
+            },
+        )
         self.validate_all(
             "CHAR(67) || CHAR(65) || CHAR(84)",
             read={
@@ -208,13 +215,13 @@ def test_clickhouse(self):
             },
         )
         self.validate_all(
-            "SELECT CAST(STR_TO_DATE('05 12 2000', '%d %m %Y') AS DATE)",
+            "SELECT CAST(STR_TO_DATE('05 12 2000', '%d %m %Y') AS Nullable(DATE))",
             read={
-                "clickhouse": "SELECT CAST(STR_TO_DATE('05 12 2000', '%d %m %Y') AS DATE)",
+                "clickhouse": "SELECT CAST(STR_TO_DATE('05 12 2000', '%d %m %Y') AS Nullable(DATE))",
                 "postgres": "SELECT TO_DATE('05 12 2000', 'DD MM YYYY')",
             },
             write={
-                "clickhouse": "SELECT CAST(STR_TO_DATE('05 12 2000', '%d %m %Y') AS DATE)",
+                "clickhouse": "SELECT CAST(STR_TO_DATE('05 12 2000', '%d %m %Y') AS Nullable(DATE))",
                 "postgres": "SELECT CAST(CAST(TO_DATE('05 12 2000', 'DD MM YYYY') AS TIMESTAMP) AS DATE)",
             },
         )
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index cac9bd5d73..9099ca578d 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -3347,3 +3347,26 @@ def test_current_schema(self):
                 "tsql": "SCHEMA_NAME()",
             },
         )
+
+    def test_integer_hex_strings(self):
+        # Hex strings such as 0xCC represent INTEGER values in the read dialects
+        integer_dialects = ("bigquery", "clickhouse")
+        for read_dialect in integer_dialects:
+            for write_dialect in (
+                "",
+                "duckdb",
+                "databricks",
+                "snowflake",
+                "spark",
+                "redshift",
+            ):
+                with self.subTest(f"Testing hex string -> INTEGER evaluation for {read_dialect}"):
+                    self.assertEqual(
+                        parse_one("SELECT 0xCC", read=read_dialect).sql(write_dialect), "SELECT 204"
+                    )
+
+            for other_integer_dialects in integer_dialects:
+                self.assertEqual(
+                    parse_one("SELECT 0xCC", read=read_dialect).sql(other_integer_dialects),
+                    "SELECT 0xCC",
+                )
diff --git a/tests/dialects/test_dune.py b/tests/dialects/test_dune.py
index 3bdd4e87f4..87a2754ca2 100644
--- a/tests/dialects/test_dune.py
+++ b/tests/dialects/test_dune.py
@@ -18,6 +18,6 @@ def test_dune(self):
             write={
                 "dune": "SELECT 0xdeadbeef",
                 "postgres": "SELECT x'deadbeef'",
-                "trino": "SELECT X'deadbeef'",
+                "trino": "SELECT x'deadbeef'",
             },
         )
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index 308ee3e693..56ada751b1 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -386,16 +386,16 @@ def test_introducers(self):
 
     def test_hexadecimal_literal(self):
         write_CC = {
-            "bigquery": "SELECT 0xCC",
-            "clickhouse": "SELECT 0xCC",
+            "bigquery": "SELECT FROM_HEX('CC')",
+            "clickhouse": UnsupportedError,
             "databricks": "SELECT X'CC'",
             "drill": "SELECT 204",
-            "duckdb": "SELECT 204",
+            "duckdb": "SELECT FROM_HEX('CC')",
             "hive": "SELECT 204",
             "mysql": "SELECT x'CC'",
             "oracle": "SELECT 204",
             "postgres": "SELECT x'CC'",
-            "presto": "SELECT 204",
+            "presto": "SELECT x'CC'",
             "redshift": "SELECT 204",
             "snowflake": "SELECT x'CC'",
             "spark": "SELECT X'CC'",
@@ -403,20 +403,20 @@ def test_hexadecimal_literal(self):
             "starrocks": "SELECT x'CC'",
             "tableau": "SELECT 204",
             "teradata": "SELECT X'CC'",
-            "trino": "SELECT X'CC'",
+            "trino": "SELECT x'CC'",
             "tsql": "SELECT 0xCC",
         }
         write_CC_with_leading_zeros = {
-            "bigquery": "SELECT 0x0000CC",
-            "clickhouse": "SELECT 0x0000CC",
+            "bigquery": "SELECT FROM_HEX('0000CC')",
+            "clickhouse": UnsupportedError,
             "databricks": "SELECT X'0000CC'",
             "drill": "SELECT 204",
-            "duckdb": "SELECT 204",
+            "duckdb": "SELECT FROM_HEX('0000CC')",
             "hive": "SELECT 204",
             "mysql": "SELECT x'0000CC'",
             "oracle": "SELECT 204",
             "postgres": "SELECT x'0000CC'",
-            "presto": "SELECT 204",
+            "presto": "SELECT x'0000CC'",
             "redshift": "SELECT 204",
             "snowflake": "SELECT x'0000CC'",
             "spark": "SELECT X'0000CC'",
@@ -424,7 +424,7 @@ def test_hexadecimal_literal(self):
             "starrocks": "SELECT x'0000CC'",
             "tableau": "SELECT 204",
             "teradata": "SELECT X'0000CC'",
-            "trino": "SELECT X'0000CC'",
+            "trino": "SELECT x'0000CC'",
             "tsql": "SELECT 0x0000CC",
         }
 
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index 1f299afd4e..1409ce3d08 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -117,6 +117,13 @@ def test_oracle(self):
             "SELECT * FROM t START WITH col CONNECT BY NOCYCLE PRIOR col1 = col2"
         )
 
+        self.validate_all(
+            "SELECT TRIM('|' FROM '||Hello ||| world||')",
+            write={
+                "clickhouse": "SELECT TRIM(BOTH '|' FROM '||Hello ||| world||')",
+                "oracle": "SELECT TRIM('|' FROM '||Hello ||| world||')",
+            },
+        )
         self.validate_all(
             "SELECT department_id, department_name INTO v_department_id, v_department_name FROM departments FETCH FIRST 1 ROWS ONLY",
             write={