Repository: tobymao/sqlglot

Issue:
MySQL: transform blob to varbinary makes a syntax error
#4835  I cannot reopen this issue, so I open a new one.
```python
>>> sql = """
... CREATE TABLE test_blob_types (
...     id INT PRIMARY KEY,
...     standard_blob BLOB,
...     tiny TINYBLOB,
...     medium MEDIUMBLOB,
...     large LONGBLOB
... );
... """
>>> ast = sqlglot.parse_one(sql, dialect="mysql")
>>> ast
Create(
  this=Schema(
    this=Table(
      this=Identifier(this=test_blob_types, quoted=False)),
    expressions=[
      ColumnDef(
        this=Identifier(this=id, quoted=False),
        kind=DataType(this=Type.INT, nested=False),
        constraints=[
          ColumnConstraint(
            kind=PrimaryKeyColumnConstraint())]),
      ColumnDef(
        this=Identifier(this=standard_blob, quoted=False),
        kind=DataType(this=Type.VARBINARY, nested=False)),
      ColumnDef(
        this=Identifier(this=tiny, quoted=False),
        kind=DataType(this=Type.TINYBLOB, nested=False)),
      ColumnDef(
        this=Identifier(this=medium, quoted=False),
        kind=DataType(this=Type.MEDIUMBLOB, nested=False)),
      ColumnDef(
        this=Identifier(this=large, quoted=False),
        kind=DataType(this=Type.LONGBLOB, nested=False))]),
  kind=TABLE)
>>> ast.sql("mysql")
'CREATE TABLE test_blob_types (id INT PRIMARY KEY, standard_blob VARBINARY, tiny TINYBLOB, medium MEDIUMBLOB, large LONGBLOB)'
```
and I execute the result in mysql client
```
CREATE TABLE test_blob_types (id INT PRIMARY KEY, standard_blob VARBINARY, tiny TINYBLOB, medium MEDIUMBLOB, large LONGBLOB)
> 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', tiny TINYBLOB, medium MEDIUMBLOB, large LONGBLOB)' at line 1
```
> blob should be transformed as varbinary(65535)

Discussion:

Solution patch:
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index 690530ee..63ba247a 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -992,6 +992,7 @@ class BigQuery(Dialect):
             exp.DataType.Type.BIGDECIMAL: "BIGNUMERIC",
             exp.DataType.Type.BIGINT: "INT64",
             exp.DataType.Type.BINARY: "BYTES",
+            exp.DataType.Type.BLOB: "BYTES",
             exp.DataType.Type.BOOLEAN: "BOOL",
             exp.DataType.Type.CHAR: "STRING",
             exp.DataType.Type.DECIMAL: "NUMERIC",
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index feb0dbc2..9aa811c1 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -920,6 +920,7 @@ class ClickHouse(Dialect):
         ARRAY_SIZE_NAME = "LENGTH"
 
         STRING_TYPE_MAPPING = {
+            exp.DataType.Type.BLOB: "String",
             exp.DataType.Type.CHAR: "String",
             exp.DataType.Type.LONGBLOB: "String",
             exp.DataType.Type.LONGTEXT: "String",
diff --git a/sqlglot/dialects/hive.py b/sqlglot/dialects/hive.py
index b5702b75..b4c4ebbc 100644
--- a/sqlglot/dialects/hive.py
+++ b/sqlglot/dialects/hive.py
@@ -486,6 +486,7 @@ class Hive(Dialect):
         TYPE_MAPPING = {
             **generator.Generator.TYPE_MAPPING,
             exp.DataType.Type.BIT: "BOOLEAN",
+            exp.DataType.Type.BLOB: "BINARY",
             exp.DataType.Type.DATETIME: "TIMESTAMP",
             exp.DataType.Type.ROWVERSION: "BINARY",
             exp.DataType.Type.TEXT: "STRING",
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index f4ef031d..927642ab 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -192,6 +192,7 @@ class MySQL(Dialect):
             "CHARSET": TokenType.CHARACTER_SET,
             # The DESCRIBE and EXPLAIN statements are synonyms.
             # https://dev.mysql.com/doc/refman/8.4/en/explain.html
+            "BLOB": TokenType.BLOB,
             "EXPLAIN": TokenType.DESCRIBE,
             "FORCE": TokenType.FORCE,
             "IGNORE": TokenType.IGNORE,
@@ -830,6 +831,7 @@ class MySQL(Dialect):
         TYPE_MAPPING.pop(exp.DataType.Type.MEDIUMTEXT)
         TYPE_MAPPING.pop(exp.DataType.Type.LONGTEXT)
         TYPE_MAPPING.pop(exp.DataType.Type.TINYTEXT)
+        TYPE_MAPPING.pop(exp.DataType.Type.BLOB)
         TYPE_MAPPING.pop(exp.DataType.Type.MEDIUMBLOB)
         TYPE_MAPPING.pop(exp.DataType.Type.LONGBLOB)
         TYPE_MAPPING.pop(exp.DataType.Type.TINYBLOB)
diff --git a/sqlglot/dialects/oracle.py b/sqlglot/dialects/oracle.py
index 0fd2be7c..837f8e73 100644
--- a/sqlglot/dialects/oracle.py
+++ b/sqlglot/dialects/oracle.py
@@ -283,6 +283,7 @@ class Oracle(Dialect):
             exp.DataType.Type.VARBINARY: "BLOB",
             exp.DataType.Type.ROWVERSION: "BLOB",
         }
+        TYPE_MAPPING.pop(exp.DataType.Type.BLOB)
 
         TRANSFORMS = {
             **generator.Generator.TRANSFORMS,
diff --git a/sqlglot/dialects/postgres.py b/sqlglot/dialects/postgres.py
index e19fee35..775617aa 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -527,6 +527,7 @@ class Postgres(Dialect):
             exp.DataType.Type.VARBINARY: "BYTEA",
             exp.DataType.Type.ROWVERSION: "BYTEA",
             exp.DataType.Type.DATETIME: "TIMESTAMP",
+            exp.DataType.Type.BLOB: "BYTEA",
         }
 
         TRANSFORMS = {
diff --git a/sqlglot/dialects/redshift.py b/sqlglot/dialects/redshift.py
index ec183271..4bebccfd 100644
--- a/sqlglot/dialects/redshift.py
+++ b/sqlglot/dialects/redshift.py
@@ -166,6 +166,7 @@ class Redshift(Postgres):
         TYPE_MAPPING = {
             **Postgres.Generator.TYPE_MAPPING,
             exp.DataType.Type.BINARY: "VARBYTE",
+            exp.DataType.Type.BLOB: "VARBYTE",
             exp.DataType.Type.INT: "INTEGER",
             exp.DataType.Type.TIMETZ: "TIME",
             exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP",
diff --git a/sqlglot/dialects/sqlite.py b/sqlglot/dialects/sqlite.py
index 73b15aad..c1cacd57 100644
--- a/sqlglot/dialects/sqlite.py
+++ b/sqlglot/dialects/sqlite.py
@@ -165,6 +165,7 @@ class SQLite(Dialect):
             exp.DataType.Type.BINARY: "BLOB",
             exp.DataType.Type.VARBINARY: "BLOB",
         }
+        TYPE_MAPPING.pop(exp.DataType.Type.BLOB)
 
         TOKEN_MAPPING = {
             TokenType.AUTO_INCREMENT: "AUTOINCREMENT",
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 677b4ef2..3b74f1d4 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -4414,6 +4414,7 @@ class DataType(Expression):
         BIGSERIAL = auto()
         BINARY = auto()
         BIT = auto()
+        BLOB = auto()
         BOOLEAN = auto()
         BPCHAR = auto()
         CHAR = auto()
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 34b93d08..b318826c 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -468,6 +468,7 @@ class Generator(metaclass=_Generator):
         exp.DataType.Type.MEDIUMTEXT: "TEXT",
         exp.DataType.Type.LONGTEXT: "TEXT",
         exp.DataType.Type.TINYTEXT: "TEXT",
+        exp.DataType.Type.BLOB: "VARBINARY",
         exp.DataType.Type.MEDIUMBLOB: "BLOB",
         exp.DataType.Type.LONGBLOB: "BLOB",
         exp.DataType.Type.TINYBLOB: "BLOB",
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 65eab913..4872adc9 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -327,6 +327,7 @@ class Parser(metaclass=_Parser):
         TokenType.TEXT,
         TokenType.MEDIUMTEXT,
         TokenType.LONGTEXT,
+        TokenType.BLOB,
         TokenType.MEDIUMBLOB,
         TokenType.LONGBLOB,
         TokenType.BINARY,
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index ddffb09a..2944debb 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -142,6 +142,7 @@ class TokenType(AutoName):
     TEXT = auto()
     MEDIUMTEXT = auto()
     LONGTEXT = auto()
+    BLOB = auto()
     MEDIUMBLOB = auto()
     LONGBLOB = auto()
     TINYBLOB = auto()


Test patch:
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index 4a28176c..173cd792 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -346,6 +346,22 @@ class TestMySQL(Validator):
                 "tsql": "CHAR(10)",
             },
         )
+        self.validate_identity("CREATE TABLE t (foo VARBINARY(5))")
+        self.validate_all(
+            "CREATE TABLE t (foo BLOB)",
+            write={
+                "mysql": "CREATE TABLE t (foo BLOB)",
+                "oracle": "CREATE TABLE t (foo BLOB)",
+                "postgres": "CREATE TABLE t (foo BYTEA)",
+                "tsql": "CREATE TABLE t (foo VARBINARY)",
+                "sqlite": "CREATE TABLE t (foo BLOB)",
+                "duckdb": "CREATE TABLE t (foo VARBINARY)",
+                "hive": "CREATE TABLE t (foo BINARY)",
+                "bigquery": "CREATE TABLE t (foo BYTES)",
+                "redshift": "CREATE TABLE t (foo VARBYTE)",
+                "clickhouse": "CREATE TABLE t (foo Nullable(String))",
+            },
+        )
 
     def test_escape(self):
         self.validate_identity("""'"abc"'""")