Repository: tobymao/sqlglot

Issue:
Parser doesn't recognize a foreign-key-clause within a column definition after unique for sqlite
When parsing a column definition with a foreign-key-clause, the parser does not expect the key word REFERENCES after UNIQUE

**Fully reproducible code snippet**
```python
from sqlglot import parse_one
s = '''
CREATE TABLE "store"
(
    store_id         INTEGER
        primary key autoincrement,
    manager_staff_id INTEGER                            not null
        unique
        references staff
            on update cascade
)
'''
parse_one(s, read="sqlite")
```

```
ParseError                                Traceback (most recent call last)
Cell In[15], line 13
      1 from sqlglot import parse_one
      2 s = '''
      3 CREATE TABLE "store"
      4 (
   (...)
     11 )
     12 '''
---> 13 parse_one(s, read="sqlite")

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/__init__.py:139, in parse_one(sql, read, dialect, into, **opts)
    137     result = dialect.parse_into(into, sql, **opts)
    138 else:
--> 139     result = dialect.parse(sql, **opts)
    141 for expression in result:
    142     if not expression:

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/dialects/dialect.py:920, in Dialect.parse(self, sql, **opts)
    919 def parse(self, sql: str, **opts) -> t.List[t.Optional[exp.Expression]]:
--> 920     return self.parser(**opts).parse(self.tokenize(sql), sql)

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:1416, in Parser.parse(self, raw_tokens, sql)
   1402 def parse(
   1403     self, raw_tokens: t.List[Token], sql: t.Optional[str] = None
   1404 ) -> t.List[t.Optional[exp.Expression]]:
   1405     """
   1406     Parses a list of tokens and returns a list of syntax trees, one tree
   1407     per parsed SQL statement.
   (...)
   1414         The list of the produced syntax trees.
   1415     """
-> 1416     return self._parse(
   1417         parse_method=self.__class__._parse_statement, raw_tokens=raw_tokens, sql=sql
   1418     )

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:1485, in Parser._parse(self, parse_method, raw_tokens, sql)
   1482 self._tokens = tokens
   1483 self._advance()
-> 1485 expressions.append(parse_method(self))
   1487 if self._index < len(self._tokens):
   1488     self.raise_error("Invalid expression / Unexpected token")

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:1717, in Parser._parse_statement(self)
   1714     return None
   1716 if self._match_set(self.STATEMENT_PARSERS):
-> 1717     return self.STATEMENT_PARSERS[self._prev.token_type](self)
   1719 if self._match_set(self.dialect.tokenizer.COMMANDS):
   1720     return self._parse_command()

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:785, in Parser.<lambda>(self)
    711 COLUMN_OPERATORS = {
    712     TokenType.DOT: None,
    713     TokenType.DCOLON: lambda self, this, to: self.expression(
   (...)
    744     ),
    745 }
    747 EXPRESSION_PARSERS = {
    748     exp.Cluster: lambda self: self._parse_sort(exp.Cluster, TokenType.CLUSTER_BY),
    749     exp.Column: lambda self: self._parse_column(),
   (...)
    775     "JOIN_TYPE": lambda self: self._parse_join_parts(),
    776 }
    778 STATEMENT_PARSERS = {
    779     TokenType.ALTER: lambda self: self._parse_alter(),
    780     TokenType.BEGIN: lambda self: self._parse_transaction(),
    781     TokenType.CACHE: lambda self: self._parse_cache(),
    782     TokenType.COMMENT: lambda self: self._parse_comment(),
    783     TokenType.COMMIT: lambda self: self._parse_commit_or_rollback(),
    784     TokenType.COPY: lambda self: self._parse_copy(),
--> 785     TokenType.CREATE: lambda self: self._parse_create(),
    786     TokenType.DELETE: lambda self: self._parse_delete(),
    787     TokenType.DESC: lambda self: self._parse_describe(),
    788     TokenType.DESCRIBE: lambda self: self._parse_describe(),
    789     TokenType.DROP: lambda self: self._parse_drop(),
    790     TokenType.GRANT: lambda self: self._parse_grant(),
    791     TokenType.INSERT: lambda self: self._parse_insert(),
    792     TokenType.KILL: lambda self: self._parse_kill(),
    793     TokenType.LOAD: lambda self: self._parse_load(),
    794     TokenType.MERGE: lambda self: self._parse_merge(),
    795     TokenType.PIVOT: lambda self: self._parse_simplified_pivot(),
    796     TokenType.PRAGMA: lambda self: self.expression(exp.Pragma, this=self._parse_expression()),
    797     TokenType.REFRESH: lambda self: self._parse_refresh(),
    798     TokenType.ROLLBACK: lambda self: self._parse_commit_or_rollback(),
    799     TokenType.SET: lambda self: self._parse_set(),
    800     TokenType.TRUNCATE: lambda self: self._parse_truncate_table(),
    801     TokenType.UNCACHE: lambda self: self._parse_uncache(),
    802     TokenType.UPDATE: lambda self: self._parse_update(),
    803     TokenType.USE: lambda self: self.expression(
    804         exp.Use,
    805         kind=self._parse_var_from_options(self.USABLES, raise_unmatched=False),
    806         this=self._parse_table(schema=False),
    807     ),
    808     TokenType.SEMICOLON: lambda self: self.expression(exp.Semicolon),
    809 }
    811 UNARY_PARSERS = {
    812     TokenType.PLUS: lambda self: self._parse_unary(),  # Unary + is handled as a no-op
    813     TokenType.NOT: lambda self: self.expression(exp.Not, this=self._parse_equality()),
   (...)
    817     TokenType.DPIPE_SLASH: lambda self: self.expression(exp.Cbrt, this=self._parse_unary()),
    818 }
    820 STRING_PARSERS = {
    821     TokenType.HEREDOC_STRING: lambda self, token: self.expression(
    822         exp.RawString, this=token.text
   (...)
    835     ),
    836 }

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:1872, in Parser._parse_create(self)
   1869 self._match(TokenType.COMMA)
   1870 extend_props(self._parse_properties(before=True))
-> 1872 this = self._parse_schema(this=table_parts)
   1874 # exp.Properties.Location.POST_SCHEMA and POST_WITH
   1875 extend_props(self._parse_properties())

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:5426, in Parser._parse_schema(self, this)
   5424     return this
   5425 args = self._parse_csv(lambda: self._parse_constraint() or self._parse_field_def())
-> 5426 self._match_r_paren()
   5427 return self.expression(exp.Schema, this=this, expressions=args)

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:7222, in Parser._match_r_paren(self, expression)
   7220 def _match_r_paren(self, expression: t.Optional[exp.Expression] = None) -> None:
   7221     if not self._match(TokenType.R_PAREN, expression=expression):
-> 7222         self.raise_error("Expecting )")

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:1529, in Parser.raise_error(self, message, token)
   1517 error = ParseError.new(
   1518     f"{message}. Line {token.line}, Col: {token.col}.\n"
   1519     f"  {start_context}\033[4m{highlight}\033[0m{end_context}",
   (...)
   1525     end_context=end_context,
   1526 )
   1528 if self.error_level == ErrorLevel.IMMEDIATE:
-> 1529     raise error
   1531 self.errors.append(error)

ParseError: Expecting ). Line 8, Col: 17.

    manager_staff_id INTEGER                            not null
        unique
        references staff
            on update cascade
)
In [16]: from sqlglot import parse_one
    ...: s = '''
    ...: CREATE TABLE "store"
    ...: (
    ...:     store_id         INTEGER
    ...:         primary key autoincrement,
    ...:     manager_staff_id INTEGER                            not null
    ...:         references staff
    ...:             on update cascade
    ...: )
    ...: '''
    ...: parse_one(s, read="sqlite")
    ...:
Out[16]:
Create(
  this=Schema(
    this=Table(
      this=Identifier(this=store, quoted=True)),
    expressions=[
      ColumnDef(
        this=Identifier(this=store_id, quoted=False),
        kind=DataType(this=Type.INT, nested=False),
        constraints=[
          ColumnConstraint(
            kind=PrimaryKeyColumnConstraint()),
          ColumnConstraint(
            kind=AutoIncrementColumnConstraint())]),
      ColumnDef(
        this=Identifier(this=manager_staff_id, quoted=False),
        kind=DataType(this=Type.INT, nested=False),
        constraints=[
          ColumnConstraint(
            kind=NotNullColumnConstraint()),
          ColumnConstraint(
            kind=Reference(
              this=Table(
                this=Identifier(this=staff, quoted=False)),
              options=[
                ON update CASCADE]))])]),
  kind=TABLE)
In [17]: from sqlglot import parse_one
    ...: s = '''
    ...: CREATE TABLE "store"
    ...: (
    ...:     store_id         INTEGER
    ...:         primary key autoincrement,
    ...:     manager_staff_id INTEGER                            not null
    ...:         unique
    ...:         references staff
    ...:             on update cascade
    ...: )
    ...: '''
    ...: parse_one(s, read="sqlite")
    ...:
---------------------------------------------------------------------------
ParseError                                Traceback (most recent call last)
Cell In[17], line 13
      1 from sqlglot import parse_one
      2 s = '''
      3 CREATE TABLE "store"
      4 (
   (...)
     11 )
     12 '''
---> 13 parse_one(s, read="sqlite")

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/__init__.py:139, in parse_one(sql, read, dialect, into, **opts)
    137     result = dialect.parse_into(into, sql, **opts)
    138 else:
--> 139     result = dialect.parse(sql, **opts)
    141 for expression in result:
    142     if not expression:

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/dialects/dialect.py:920, in Dialect.parse(self, sql, **opts)
    919 def parse(self, sql: str, **opts) -> t.List[t.Optional[exp.Expression]]:
--> 920     return self.parser(**opts).parse(self.tokenize(sql), sql)

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:1416, in Parser.parse(self, raw_tokens, sql)
   1402 def parse(
   1403     self, raw_tokens: t.List[Token], sql: t.Optional[str] = None
   1404 ) -> t.List[t.Optional[exp.Expression]]:
   1405     """
   1406     Parses a list of tokens and returns a list of syntax trees, one tree
   1407     per parsed SQL statement.
   (...)
   1414         The list of the produced syntax trees.
   1415     """
-> 1416     return self._parse(
   1417         parse_method=self.__class__._parse_statement, raw_tokens=raw_tokens, sql=sql
   1418     )

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:1485, in Parser._parse(self, parse_method, raw_tokens, sql)
   1482 self._tokens = tokens
   1483 self._advance()
-> 1485 expressions.append(parse_method(self))
   1487 if self._index < len(self._tokens):
   1488     self.raise_error("Invalid expression / Unexpected token")

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:1717, in Parser._parse_statement(self)
   1714     return None
   1716 if self._match_set(self.STATEMENT_PARSERS):
-> 1717     return self.STATEMENT_PARSERS[self._prev.token_type](self)
   1719 if self._match_set(self.dialect.tokenizer.COMMANDS):
   1720     return self._parse_command()

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:785, in Parser.<lambda>(self)
    711 COLUMN_OPERATORS = {
    712     TokenType.DOT: None,
    713     TokenType.DCOLON: lambda self, this, to: self.expression(
   (...)
    744     ),
    745 }
    747 EXPRESSION_PARSERS = {
    748     exp.Cluster: lambda self: self._parse_sort(exp.Cluster, TokenType.CLUSTER_BY),
    749     exp.Column: lambda self: self._parse_column(),
   (...)
    775     "JOIN_TYPE": lambda self: self._parse_join_parts(),
    776 }
    778 STATEMENT_PARSERS = {
    779     TokenType.ALTER: lambda self: self._parse_alter(),
    780     TokenType.BEGIN: lambda self: self._parse_transaction(),
    781     TokenType.CACHE: lambda self: self._parse_cache(),
    782     TokenType.COMMENT: lambda self: self._parse_comment(),
    783     TokenType.COMMIT: lambda self: self._parse_commit_or_rollback(),
    784     TokenType.COPY: lambda self: self._parse_copy(),
--> 785     TokenType.CREATE: lambda self: self._parse_create(),
    786     TokenType.DELETE: lambda self: self._parse_delete(),
    787     TokenType.DESC: lambda self: self._parse_describe(),
    788     TokenType.DESCRIBE: lambda self: self._parse_describe(),
    789     TokenType.DROP: lambda self: self._parse_drop(),
    790     TokenType.GRANT: lambda self: self._parse_grant(),
    791     TokenType.INSERT: lambda self: self._parse_insert(),
    792     TokenType.KILL: lambda self: self._parse_kill(),
    793     TokenType.LOAD: lambda self: self._parse_load(),
    794     TokenType.MERGE: lambda self: self._parse_merge(),
    795     TokenType.PIVOT: lambda self: self._parse_simplified_pivot(),
    796     TokenType.PRAGMA: lambda self: self.expression(exp.Pragma, this=self._parse_expression()),
    797     TokenType.REFRESH: lambda self: self._parse_refresh(),
    798     TokenType.ROLLBACK: lambda self: self._parse_commit_or_rollback(),
    799     TokenType.SET: lambda self: self._parse_set(),
    800     TokenType.TRUNCATE: lambda self: self._parse_truncate_table(),
    801     TokenType.UNCACHE: lambda self: self._parse_uncache(),
    802     TokenType.UPDATE: lambda self: self._parse_update(),
    803     TokenType.USE: lambda self: self.expression(
    804         exp.Use,
    805         kind=self._parse_var_from_options(self.USABLES, raise_unmatched=False),
    806         this=self._parse_table(schema=False),
    807     ),
    808     TokenType.SEMICOLON: lambda self: self.expression(exp.Semicolon),
    809 }
    811 UNARY_PARSERS = {
    812     TokenType.PLUS: lambda self: self._parse_unary(),  # Unary + is handled as a no-op
    813     TokenType.NOT: lambda self: self.expression(exp.Not, this=self._parse_equality()),
   (...)
    817     TokenType.DPIPE_SLASH: lambda self: self.expression(exp.Cbrt, this=self._parse_unary()),
    818 }
    820 STRING_PARSERS = {
    821     TokenType.HEREDOC_STRING: lambda self, token: self.expression(
    822         exp.RawString, this=token.text
   (...)
    835     ),
    836 }

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:1872, in Parser._parse_create(self)
   1869 self._match(TokenType.COMMA)
   1870 extend_props(self._parse_properties(before=True))
-> 1872 this = self._parse_schema(this=table_parts)
   1874 # exp.Properties.Location.POST_SCHEMA and POST_WITH
   1875 extend_props(self._parse_properties())

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:5426, in Parser._parse_schema(self, this)
   5424     return this
   5425 args = self._parse_csv(lambda: self._parse_constraint() or self._parse_field_def())
-> 5426 self._match_r_paren()
   5427 return self.expression(exp.Schema, this=this, expressions=args)

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:7222, in Parser._match_r_paren(self, expression)
   7220 def _match_r_paren(self, expression: t.Optional[exp.Expression] = None) -> None:
   7221     if not self._match(TokenType.R_PAREN, expression=expression):
-> 7222         self.raise_error("Expecting )")

File ~/.local/share/conda/envs/tuning/lib/python3.12/site-packages/sqlglot/parser.py:1529, in Parser.raise_error(self, message, token)
   1517 error = ParseError.new(
   1518     f"{message}. Line {token.line}, Col: {token.col}.\n"
   1519     f"  {start_context}\033[4m{highlight}\033[0m{end_context}",
   (...)
   1525     end_context=end_context,
   1526 )
   1528 if self.error_level == ErrorLevel.IMMEDIATE:
-> 1529     raise error
   1531 self.errors.append(error)

ParseError: Expecting ). Line 8, Col: 17.

    manager_staff_id INTEGER                            not null
        unique
        references staff
            on update cascade
)
```


Discussion:
VaggelisD: It's actually not clear what's the behavior leading to the 2nd issue; If it was an alias, the `AS` version wouldn't lead to an error and it would be possible to select it directly. Here are some interesting examples of that:

- It's valid to include multiple identifiers before the type
```SQL
sqlite> create table test (foo bar baz TEXT); 
sqlite> insert into test values (1);
sqlite> select * from test;
1
```

- The identifier resolution rules are not obvious; It seems that the column can be selected with a subset of the text 
```
sqlite> select foo bar baz from test;
Parse error: near "baz": syntax error
  select foo bar baz from test;
                 ^--- error here

sqlite> select foo bar from test;
1

sqlite> select foo from test;
1

sqlite> select bar from test;
Parse error: no such column: bar
  select bar from test;
         ^--- error here

sqlite> select baz from test;
Parse error: no such column: baz
  select baz from test;
         ^--- error here

```

- Upon inspecting the table definition, the 2nd+ identifier(s) leak into the type (?)
```SQL
sqlite> PRAGMA table_info("test");
0|foo|bar baz TEXT|0||0
```

For these reasons, I'll revert the solution to (2)

Patch:
diff --git a/sqlglot/dialects/sqlite.py b/sqlglot/dialects/sqlite.py
index d7126634..02698809 100644
--- a/sqlglot/dialects/sqlite.py
+++ b/sqlglot/dialects/sqlite.py
@@ -120,6 +120,14 @@ class SQLite(Dialect):
         }
         STRING_ALIASES = True
 
+        def _parse_unique(self) -> exp.UniqueColumnConstraint:
+            # Do not consume more tokens if UNIQUE is used as a standalone constraint, e.g:
+            # CREATE TABLE foo (bar TEXT UNIQUE REFERENCES baz ...)
+            if self._curr.text.upper() in self.CONSTRAINT_PARSERS:
+                return self.expression(exp.UniqueColumnConstraint)
+
+            return super()._parse_unique()
+
     class Generator(generator.Generator):
         JOIN_HINTS = False
         TABLE_HINTS = False


Test patch:
diff --git a/tests/dialects/test_sqlite.py b/tests/dialects/test_sqlite.py
index 230c0e8f..e37cdc8e 100644
--- a/tests/dialects/test_sqlite.py
+++ b/tests/dialects/test_sqlite.py
@@ -222,3 +222,7 @@ class TestSQLite(Validator):
                 "mysql": "CREATE TABLE `x` (`Name` VARCHAR(200) NOT NULL)",
             },
         )
+
+        self.validate_identity(
+            "CREATE TABLE store (store_id INTEGER PRIMARY KEY AUTOINCREMENT, mgr_id INTEGER NOT NULL UNIQUE REFERENCES staff ON UPDATE CASCADE)"
+        )