Repository: tobymao/sqlglot
Pull request: fix(sqlite): transpile double quoted PRIMARY KEY
Fixes #4938

**DOCS**
[SQLite double quotes identifier](https://www.sqlite.org/quirks.html#double_quoted_string_literals_are_accepted)

Problem statement:
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: ''
```

Hints from issue discussion:
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

Thanks, @geooo109 can you take a quick look? Should be easy to fix.

Commit message: fix test

Patch to review:
diff --git a/sqlglot/dialects/sqlite.py b/sqlglot/dialects/sqlite.py
index 659ba64492..9d67dd223e 100644
--- a/sqlglot/dialects/sqlite.py
+++ b/sqlglot/dialects/sqlite.py
@@ -44,7 +44,9 @@ def _transform_create(expression: exp.Expression) -> exp.Expression:
                 primary_key = e
 
         if primary_key and len(primary_key.expressions) == 1:
-            column = defs[primary_key.expressions[0].name]
+            expr = primary_key.expressions[0]
+            name = expr.this.name if isinstance(expr, exp.Ordered) else expr.name
+            column = defs[name]
             column.append(
                 "constraints", exp.ColumnConstraint(kind=exp.PrimaryKeyColumnConstraint())
             )
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')")


Code review comments:
diff_hunk: @@ -44,7 +44,9 @@ def _transform_create(expression: exp.Expression) -> exp.Expression:
                 primary_key = e
 
         if primary_key and len(primary_key.expressions) == 1:
-            column = defs[primary_key.expressions[0].name]
+            expr = primary_key.expressions[0]
+            name = expr.this.name if isinstance(expr, exp.Ordered) else expr.name
+            column = defs[name]
path: sqlglot/dialects/sqlite.py
text: @user2:
I'd suggest adding this to `ordered` instead and reverting these changes.

```python
    @user1
    def name(self) -> str:
        return self.this.name
```

@author:
Yep, cleaner (thought it would break something in the future, but this isn't the case the codebase contains this logic in various places)