Repository: tobymao/sqlglot
Pull request: Fix(hive): don't generate BYTE when transpiling Oracle's VARCHAR(5 BYTE)
Fixes #2356

This shouldn't be breaking, I remember adding support for the `expression` argument only to handle Oracle's `BYTE`, `CHAR` type specifiers, see https://github.com/tobymao/sqlglot/commit/3ee16c5e8

Problem statement:
Handle BYTE statement in Oracle DDL to Spark
using sqlglot to transpile some Oracle DDL to Spark that I have from a customer.  The modified code changed the `VARCHAR2(5 BYTE)` to `VACHAR(5 BYTE)` but the DDL still fails in Spark with the `(5 BYTE)`.   Once removing the word BYTE the DDL executed fine.

Hints from issue discussion:

Commit message: Fix(hive): don't generate BYTE when transpiling Oracle's VARCHAR(5 BYTE)

Patch to review:
diff --git a/sqlglot/dialects/hive.py b/sqlglot/dialects/hive.py
index 3f925a708f..828ea32386 100644
--- a/sqlglot/dialects/hive.py
+++ b/sqlglot/dialects/hive.py
@@ -562,13 +562,18 @@ def datatype_sql(self, expression: exp.DataType) -> str:
                 expression = exp.DataType.build("text")
             elif expression.this in exp.DataType.TEMPORAL_TYPES:
                 expression = exp.DataType.build(expression.this)
-            elif expression.is_type("float"):
-                size_expression = expression.find(exp.DataTypeParam)
-                if size_expression:
+
+            size_expression = expression.find(exp.DataTypeParam)
+            if size_expression:
+                if expression.is_type("float"):
                     size = int(size_expression.name)
                     expression = (
                         exp.DataType.build("float") if size <= 32 else exp.DataType.build("double")
                     )
+                elif size_expression.expression:
+                    # This removes keywords like `BYTE` (Oracle) in `CAST(x AS VARCHAR(5 BYTE))`
+                    expression = expression.copy()
+                    t.cast(exp.DataTypeParam, expression.find(exp.DataTypeParam)).expression.pop()
 
             return super().datatype_sql(expression)
 
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index 675ee8a452..5572ec1014 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -22,8 +22,6 @@ def test_oracle(self):
         self.validate_identity("SELECT * FROM t FOR UPDATE OF s.t.c, s.t.v SKIP LOCKED")
         self.validate_identity("SELECT STANDARD_HASH('hello')")
         self.validate_identity("SELECT STANDARD_HASH('hello', 'MD5')")
-        self.validate_identity("SELECT CAST(NULL AS VARCHAR2(2328 CHAR)) AS COL1")
-        self.validate_identity("SELECT CAST(NULL AS VARCHAR2(2328 BYTE)) AS COL1")
         self.validate_identity("SELECT * FROM table_name@dblink_name.database_link_domain")
         self.validate_identity("SELECT * FROM table_name SAMPLE (25) s")
         self.validate_identity("SELECT * FROM V$SESSION")
@@ -60,6 +58,20 @@ def test_oracle(self):
             "SELECT * FROM t SAMPLE (0.25)",
         )
 
+        self.validate_all(
+            "SELECT CAST(NULL AS VARCHAR2(2328 CHAR)) AS COL1",
+            write={
+                "oracle": "SELECT CAST(NULL AS VARCHAR2(2328 CHAR)) AS COL1",
+                "spark": "SELECT CAST(NULL AS VARCHAR(2328)) AS COL1",
+            },
+        )
+        self.validate_all(
+            "SELECT CAST(NULL AS VARCHAR2(2328 BYTE)) AS COL1",
+            write={
+                "oracle": "SELECT CAST(NULL AS VARCHAR2(2328 BYTE)) AS COL1",
+                "spark": "SELECT CAST(NULL AS VARCHAR(2328)) AS COL1",
+            },
+        )
         self.validate_all(
             "NVL(NULL, 1)",
             write={


Code review comments:
diff_hunk: @@ -562,13 +562,18 @@ def datatype_sql(self, expression: exp.DataType) -> str:
                 expression = exp.DataType.build("text")
             elif expression.this in exp.DataType.TEMPORAL_TYPES:
                 expression = exp.DataType.build(expression.this)
-            elif expression.is_type("float"):
-                size_expression = expression.find(exp.DataTypeParam)
-                if size_expression:
+
+            size_expression = expression.find(exp.DataTypeParam)
+            if size_expression:
+                if expression.is_type("float"):
                     size = int(size_expression.name)
                     expression = (
                         exp.DataType.build("float") if size <= 32 else exp.DataType.build("double")
                     )
+                elif size_expression.expression:
+                    # This removes keywords like `BYTE` (Oracle) in `CAST(x AS VARCHAR(5 BYTE))`
+                    expression = expression.copy()
+                    t.cast(exp.DataTypeParam, expression.find(exp.DataTypeParam)).expression.pop()
path: sqlglot/dialects/hive.py
text: @author:
On second thought, maybe we should control this using a generator flag instead so that we only generate these specifiers for Oracle?

@user1:
sure

@author:
Done