Repository: tobymao/sqlglot

Issue:
Crash while reformatting a valid Sqlite statement
This piece of code used to run perfectly for months, at least until `sqlglot 26.2.1`.

After a recent update of our `sqlglot` version, it fails. Bisection shows the issue appears specifically with` sqlglot 26.3.0` (and upper versions).

The code to reproduce the error is:

```
import sqlglot

def standardize_sql(dialect: str, sql: str) -> str:
    standard_sqls = sqlglot.transpile(
        sql, read=dialect, pretty=True,
    )
    standard_sql = "\n".join(standard_sqls)
    return standard_sql

print(
    standardize_sql("sqlite", """
        CREATE TABLE "Component last run" (
            "Component ID" TEXT NOT NULL,
            "Date-time" DATETIME NOT NULL,
            "Timestamp" DATETIME NOT NULL DEFAULT (
                STRFTIME('%Y-%m-%d %H:%M:%f+00:00', 'now')
            ),
            PRIMARY KEY ("Component ID")
        )
    """)
)
```

which gives with `sqlglot 26.3.0` and upper versions:

```
...
File /opt/homebrew/Cellar/micromamba/2.0.8/envs/work/lib/python3.12/site-packages/sqlglot/dialects/sqlite.py:61, in _transform_create(expression)
     58         primary_key = e
     60 if primary_key and len(primary_key.expressions) == 1:
---> 61     column = defs[primary_key.expressions[0].name]
     62     column.append(
     63         "constraints", exp.ColumnConstraint(kind=exp.PrimaryKeyColumnConstraint())
     64     )
     65     schema.expressions.remove(primary_key)

KeyError: ''
```

Discussion:
@rgeronimi: Further investigation shows sqlglot running fine if the PRIMARY KEY column name is quoted with single quotes rather than double quotes. This is however not standard : Sqlite's documentation states that column names shall be double quoted, and that it comes from the SQL standard:

https://www.sqlite.org/quirks.html#double_quoted_string_literals_are_accepted

Indeed it seems `sqlglot 26.3.0` did include a change to the PRIMARY KEY parsing code - I am reaching the limits of my understanding of sqlglot's code:

https://github.com/tobymao/sqlglot/commit/14474ee689025cc67b1f9a07e51d2f414ec5ab49
@georgesittas: Thanks, @geooo109 can you take a quick look? Should be easy to fix.

Solution patch:
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 4a2531bd5f..531b740fd0 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -2660,6 +2660,10 @@ class Sort(Order):
 class Ordered(Expression):
     arg_types = {"this": True, "desc": False, "nulls_first": True, "with_fill": False}
 
+    @property
+    def name(self) -> str:
+        return self.this.name
+
 
 class Property(Expression):
     arg_types = {"this": True, "value": True}


Test patch:
diff --git a/tests/dialects/test_sqlite.py b/tests/dialects/test_sqlite.py
index ce5185b443..e34c01488d 100644
--- a/tests/dialects/test_sqlite.py
+++ b/tests/dialects/test_sqlite.py
@@ -109,6 +109,10 @@ def test_sqlite(self):
             "SELECT * FROM station WHERE NOT city IS ''",
         )
         self.validate_identity("SELECT JSON_OBJECT('col1', 1, 'col2', '1')")
+        self.validate_identity(
+            'CREATE TABLE "foo t" ("foo t id" TEXT NOT NULL, PRIMARY KEY ("foo t id"))',
+            'CREATE TABLE "foo t" ("foo t id" TEXT NOT NULL PRIMARY KEY)',
+        )
 
     def test_strftime(self):
         self.validate_identity("SELECT STRFTIME('%Y/%m/%d', 'now')")