Repository: tobymao/sqlglot

Issue:
getting error for foreign key constrain for mysql 
code =  from sqlglot import parse_one,dialects
sql = """
CREATE TABLE IF NOT EXISTS `industry_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `industry_id` bigint(20) NOT NULL,
  `industry_column_1` varchar(1000) ,
  `industry_column_2` varchar(1000) ,
  `industry_column_3` varchar(1000) ,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_industry_id` (`industry_id`),
  KEY `fk_industry_info_id` (`industry_id`)
) ;
     """
s = parse_one(sql, dialect = dialects.Dialects.MYSQL)
print(s)

error        -        raise error
sqlglot.errors.ParseError: Expecting ). Line 10, Col: 27.
  n_3` varchar(1000) ,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_industry_id` (`industry_id`),
  KEY `fk_industry_info_id` (`industry_id`)
) ;
**mainly the error is because of this foreign key constraint line   KEY `fk_industry_info_id` (`industry_id`)** 

Discussion:

Patch:
diff --git a/sqlglot/dataframe/sql/functions.py b/sqlglot/dataframe/sql/functions.py
index 1549a075..4002cfe4 100644
--- a/sqlglot/dataframe/sql/functions.py
+++ b/sqlglot/dataframe/sql/functions.py
@@ -386,7 +386,7 @@ def input_file_name() -> Column:
 
 
 def isnan(col: ColumnOrName) -> Column:
-    return Column.invoke_anonymous_function(col, "ISNAN")
+    return Column.invoke_expression_over_column(col, expression.IsNan)
 
 
 def isnull(col: ColumnOrName) -> Column:
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index 8f60df24..ce1a4860 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -380,7 +380,7 @@ class ClickHouse(Dialect):
             ]
 
         def parameterizedagg_sql(self, expression: exp.Anonymous) -> str:
-            params = self.expressions(expression, "params", flat=True)
+            params = self.expressions(expression, key="params", flat=True)
             return self.func(expression.name, *expression.expressions) + f"({params})"
 
         def placeholder_sql(self, expression: exp.Placeholder) -> str:
diff --git a/sqlglot/dialects/hive.py b/sqlglot/dialects/hive.py
index e1314346..c91c4ba1 100644
--- a/sqlglot/dialects/hive.py
+++ b/sqlglot/dialects/hive.py
@@ -396,6 +396,7 @@ class Hive(Dialect):
             exp.FromBase64: rename_func("UNBASE64"),
             exp.If: if_sql,
             exp.ILike: no_ilike_sql,
+            exp.IsNan: rename_func("ISNAN"),
             exp.JSONExtract: rename_func("GET_JSON_OBJECT"),
             exp.JSONExtractScalar: rename_func("GET_JSON_OBJECT"),
             exp.JSONFormat: _json_format_sql,
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index 5d65f77e..28bbb99f 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -303,6 +303,22 @@ class MySQL(Dialect):
             "NAMES": lambda self: self._parse_set_item_names(),
         }
 
+        CONSTRAINT_PARSERS = {
+            **parser.Parser.CONSTRAINT_PARSERS,
+            "FULLTEXT": lambda self: self._parse_index_constraint(kind="FULLTEXT"),
+            "INDEX": lambda self: self._parse_index_constraint(),
+            "KEY": lambda self: self._parse_index_constraint(),
+            "SPATIAL": lambda self: self._parse_index_constraint(kind="SPATIAL"),
+        }
+
+        SCHEMA_UNNAMED_CONSTRAINTS = {
+            *parser.Parser.SCHEMA_UNNAMED_CONSTRAINTS,
+            "FULLTEXT",
+            "INDEX",
+            "KEY",
+            "SPATIAL",
+        }
+
         PROFILE_TYPES = {
             "ALL",
             "BLOCK IO",
@@ -327,6 +343,57 @@ class MySQL(Dialect):
 
         LOG_DEFAULTS_TO_LN = True
 
+        def _parse_index_constraint(
+            self, kind: t.Optional[str] = None
+        ) -> exp.IndexColumnConstraint:
+            if kind:
+                self._match_texts({"INDEX", "KEY"})
+
+            this = self._parse_id_var(any_token=False)
+            type_ = self._match(TokenType.USING) and self._advance_any() and self._prev.text
+            schema = self._parse_schema()
+
+            options = []
+            while True:
+                if self._match_text_seq("KEY_BLOCK_SIZE"):
+                    self._match(TokenType.EQ)
+                    opt = exp.IndexConstraintOption(key_block_size=self._parse_number())
+                elif self._match(TokenType.USING):
+                    opt = exp.IndexConstraintOption(using=self._advance_any() and self._prev.text)
+                elif self._match_text_seq("WITH", "PARSER"):
+                    opt = exp.IndexConstraintOption(parser=self._parse_var(any_token=True))
+                elif self._match(TokenType.COMMENT):
+                    opt = exp.IndexConstraintOption(comment=self._parse_string())
+                elif self._match_text_seq("VISIBLE"):
+                    opt = exp.IndexConstraintOption(visible=True)
+                elif self._match_text_seq("INVISIBLE"):
+                    opt = exp.IndexConstraintOption(visible=False)
+                elif self._match_text_seq("ENGINE_ATTRIBUTE"):
+                    self._match(TokenType.EQ)
+                    opt = exp.IndexConstraintOption(engine_attr=self._parse_string())
+                elif self._match_text_seq("ENGINE_ATTRIBUTE"):
+                    self._match(TokenType.EQ)
+                    opt = exp.IndexConstraintOption(engine_attr=self._parse_string())
+                elif self._match_text_seq("SECONDARY_ENGINE_ATTRIBUTE"):
+                    self._match(TokenType.EQ)
+                    opt = exp.IndexConstraintOption(secondary_engine_attr=self._parse_string())
+                else:
+                    opt = None
+
+                if not opt:
+                    break
+
+                options.append(opt)
+
+            return self.expression(
+                exp.IndexColumnConstraint,
+                this=this,
+                schema=schema,
+                kind=kind,
+                type=type_,
+                options=options,
+            )
+
         def _parse_show_mysql(
             self,
             this: str,
diff --git a/sqlglot/dialects/presto.py b/sqlglot/dialects/presto.py
index 265c6e58..14ec3dd5 100644
--- a/sqlglot/dialects/presto.py
+++ b/sqlglot/dialects/presto.py
@@ -192,6 +192,8 @@ class Presto(Dialect):
             "START": TokenType.BEGIN,
             "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE,
             "ROW": TokenType.STRUCT,
+            "IPADDRESS": TokenType.IPADDRESS,
+            "IPPREFIX": TokenType.IPPREFIX,
         }
 
     class Parser(parser.Parser):
diff --git a/sqlglot/dialects/spark.py b/sqlglot/dialects/spark.py
index 73f43700..b9aaa662 100644
--- a/sqlglot/dialects/spark.py
+++ b/sqlglot/dialects/spark.py
@@ -3,6 +3,7 @@ from __future__ import annotations
 import typing as t
 
 from sqlglot import exp
+from sqlglot.dialects.dialect import rename_func
 from sqlglot.dialects.spark2 import Spark2
 from sqlglot.helper import seq_get
 
@@ -47,7 +48,11 @@ class Spark(Spark2):
             exp.DataType.Type.SMALLMONEY: "DECIMAL(6, 4)",
             exp.DataType.Type.UNIQUEIDENTIFIER: "STRING",
         }
-        TRANSFORMS = Spark2.Generator.TRANSFORMS.copy()
+
+        TRANSFORMS = {
+            **Spark2.Generator.TRANSFORMS,
+            exp.StartsWith: rename_func("STARTSWITH"),
+        }
         TRANSFORMS.pop(exp.DateDiff)
         TRANSFORMS.pop(exp.Group)
 
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index ccdc8433..811b6fdb 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -1206,6 +1206,19 @@ class MergeTreeTTL(Expression):
     }
 
 
+# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
+class IndexConstraintOption(Expression):
+    arg_types = {
+        "key_block_size": False,
+        "using": False,
+        "parser": False,
+        "comment": False,
+        "visible": False,
+        "engine_attr": False,
+        "secondary_engine_attr": False,
+    }
+
+
 class ColumnConstraint(Expression):
     arg_types = {"this": False, "kind": True}
 
@@ -1272,6 +1285,11 @@ class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
     }
 
 
+# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
+class IndexColumnConstraint(ColumnConstraintKind):
+    arg_types = {"this": False, "schema": True, "kind": False, "type": False, "options": False}
+
+
 class InlineLengthColumnConstraint(ColumnConstraintKind):
     pass
 
@@ -3377,6 +3395,8 @@ class DataType(Expression):
         HSTORE = auto()
         IMAGE = auto()
         INET = auto()
+        IPADDRESS = auto()
+        IPPREFIX = auto()
         INT = auto()
         INT128 = auto()
         INT256 = auto()
@@ -4264,6 +4284,10 @@ class Initcap(Func):
     arg_types = {"this": True, "expression": False}
 
 
+class IsNan(Func):
+    _sql_names = ["IS_NAN", "ISNAN"]
+
+
 class JSONKeyValue(Expression):
     arg_types = {"this": True, "expression": True}
 
@@ -4550,6 +4574,11 @@ class StandardHash(Func):
     arg_types = {"this": True, "expression": False}
 
 
+class StartsWith(Func):
+    _sql_names = ["STARTS_WITH", "STARTSWITH"]
+    arg_types = {"this": True, "expression": True}
+
+
 class StrPosition(Func):
     arg_types = {
         "this": True,
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index da5083e1..e728245d 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -2569,6 +2569,51 @@ class Generator:
         record_reader = f" RECORDREADER {record_reader}" if record_reader else ""
         return f"{transform}{row_format_before}{record_writer}{using}{schema}{row_format_after}{record_reader}"
 
+    def indexconstraintoption_sql(self, expression: exp.IndexConstraintOption) -> str:
+        key_block_size = self.sql(expression, "key_block_size")
+        if key_block_size:
+            return f"KEY_BLOCK_SIZE = {key_block_size}"
+
+        using = self.sql(expression, "using")
+        if using:
+            return f"USING {using}"
+
+        parser = self.sql(expression, "parser")
+        if parser:
+            return f"WITH PARSER {parser}"
+
+        comment = self.sql(expression, "comment")
+        if comment:
+            return f"COMMENT {comment}"
+
+        visible = expression.args.get("visible")
+        if visible is not None:
+            return "VISIBLE" if visible else "INVISIBLE"
+
+        engine_attr = self.sql(expression, "engine_attr")
+        if engine_attr:
+            return f"ENGINE_ATTRIBUTE = {engine_attr}"
+
+        secondary_engine_attr = self.sql(expression, "secondary_engine_attr")
+        if secondary_engine_attr:
+            return f"SECONDARY_ENGINE_ATTRIBUTE = {secondary_engine_attr}"
+
+        self.unsupported("Unsupported index constraint option.")
+        return ""
+
+    def indexcolumnconstraint_sql(self, expression: exp.IndexColumnConstraint) -> str:
+        kind = self.sql(expression, "kind")
+        kind = f"{kind} INDEX" if kind else "INDEX"
+        this = self.sql(expression, "this")
+        this = f" {this}" if this else ""
+        type_ = self.sql(expression, "type")
+        type_ = f" USING {type_}" if type_ else ""
+        schema = self.sql(expression, "schema")
+        schema = f" {schema}" if schema else ""
+        options = self.expressions(expression, key="options", sep=" ")
+        options = f" {options}" if options else ""
+        return f"{kind}{this}{type_}{schema}{options}"
+
 
 def cached_generator(
     cache: t.Optional[t.Dict[int, str]] = None
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 90f179fd..5b2e091f 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -185,6 +185,8 @@ class Parser(metaclass=_Parser):
         TokenType.VARIANT,
         TokenType.OBJECT,
         TokenType.INET,
+        TokenType.IPADDRESS,
+        TokenType.IPPREFIX,
         TokenType.ENUM,
         *NESTED_TYPE_TOKENS,
     }
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index a19ebaa3..729e47fa 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -147,6 +147,8 @@ class TokenType(AutoName):
     VARIANT = auto()
     OBJECT = auto()
     INET = auto()
+    IPADDRESS = auto()
+    IPPREFIX = auto()
     ENUM = auto()
 
     # keywords


Test patch:
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index ae2fa415..0bc474da 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -9,6 +9,12 @@ class TestMySQL(Validator):
         self.validate_identity("CREATE TABLE foo (id BIGINT)")
         self.validate_identity("UPDATE items SET items.price = 0 WHERE items.id >= 5 LIMIT 10")
         self.validate_identity("DELETE FROM t WHERE a <= 10 LIMIT 10")
+        self.validate_identity("CREATE TABLE foo (a BIGINT, INDEX USING BTREE (b))")
+        self.validate_identity("CREATE TABLE foo (a BIGINT, FULLTEXT INDEX (b))")
+        self.validate_identity("CREATE TABLE foo (a BIGINT, SPATIAL INDEX (b))")
+        self.validate_identity(
+            "CREATE TABLE foo (a BIGINT, INDEX b USING HASH (c) COMMENT 'd' VISIBLE ENGINE_ATTRIBUTE = 'e' WITH PARSER foo)"
+        )
         self.validate_identity(
             "DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.id IS NULL"
         )
@@ -67,6 +73,12 @@ class TestMySQL(Validator):
                 "mysql": "CREATE TABLE `foo` (`id` CHAR(36) NOT NULL DEFAULT (UUID()), PRIMARY KEY (`id`), UNIQUE `id` (`id`))",
             },
         )
+        self.validate_all(
+            "CREATE TABLE IF NOT EXISTS industry_info (a BIGINT(20) NOT NULL AUTO_INCREMENT, b BIGINT(20) NOT NULL, c VARCHAR(1000), PRIMARY KEY (a), UNIQUE KEY d (b), KEY e (b))",
+            write={
+                "mysql": "CREATE TABLE IF NOT EXISTS industry_info (a BIGINT(20) NOT NULL AUTO_INCREMENT, b BIGINT(20) NOT NULL, c VARCHAR(1000), PRIMARY KEY (a), UNIQUE d (b), INDEX e (b))",
+            },
+        )
 
     def test_identity(self):
         self.validate_identity("SELECT 1 XOR 0")
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index c0b77a38..a2800bdd 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -109,6 +109,8 @@ class TestPresto(Validator):
                 "spark": "CAST(x AS TIMESTAMP)",
             },
         )
+        self.validate_identity("CAST(x AS IPADDRESS)")
+        self.validate_identity("CAST(x AS IPPREFIX)")
 
     def test_regex(self):
         self.validate_all(
@@ -459,6 +461,25 @@ class TestPresto(Validator):
         self.validate_identity("START TRANSACTION ISOLATION LEVEL REPEATABLE READ")
         self.validate_identity("APPROX_PERCENTILE(a, b, c, d)")
 
+        self.validate_all(
+            "STARTS_WITH('abc', 'a')",
+            read={"spark": "STARTSWITH('abc', 'a')"},
+            write={
+                "presto": "STARTS_WITH('abc', 'a')",
+                "spark": "STARTSWITH('abc', 'a')",
+            },
+        )
+        self.validate_all(
+            "IS_NAN(x)",
+            read={
+                "spark": "ISNAN(x)",
+            },
+            write={
+                "presto": "IS_NAN(x)",
+                "spark": "ISNAN(x)",
+                "spark2": "ISNAN(x)",
+            },
+        )
         self.validate_all("VALUES 1, 2, 3", write={"presto": "VALUES (1), (2), (3)"})
         self.validate_all("INTERVAL '1 day'", write={"trino": "INTERVAL '1' day"})
         self.validate_all("(5 * INTERVAL '7' day)", read={"": "INTERVAL '5' week"})