Repository: tobymao/sqlglot

Issue:
Incorrect re-quoting in redshift COPY commands
Issue following on from slack discussion here:
https://tobiko-data.slack.com/archives/C044BRE5W4S/p1763453787937409

**Dialect (read & write):** redshift

I seem to have come up against a bug in the way sqlglot parses and normalizes quoting in redshift `COPY` commands, I'm seeing this whilst attempting to use a copy command in a sqlmesh model but have discussed with tobiko peeps who indicated sqlglot would be the right place to raise the issue (see slack link above).

A valid example of a copy statement, loading a avro data from an s3 partition,  would be:
```
DROP TABLE IF EXISTS test_staging_tbl;

CREATE TEMP
TABLE test_staging_tbl (
    id BIGINT
    -- any other columns here
);

COPY test_staging_tbl
FROM 's3://your/bucket/prefix/here' IAM_ROLE default FORMAT AS AVRO 'auto';

SELECT * FROM test_staging_tbl
```

This works fine when executed directly against redshift, but when used within a sqlmesh model, by the time it has gone through sqlglot's parsing and sqlmesh executes it against redshift, it has wrapped default in double quotes and removed the single quotes around auto, both of which make the statement invalid, like so:
```
COPY test_staging_tbl
FROM 's3://your/bucket/prefix/here' IAM_ROLE "default" FORMAT AS AVRO auto;
```

I have managed to work around this by using an explicit iam role reference (rather than using the default keyword) and double quoting auto like so:
```
COPY test_staging_tbl
FROM 's3://your/bucket/prefix/here' IAM_ROLE 'arn:aws:iam::my_account_id:role/my_role_name' FORMAT AS AVRO '\'auto\'';
```

But this isn't ideal as it means i have to inject the correct default role as a variable, or something along those lines and the double quoting needs a bit of explaination and translation when copying back to a redshift console.


**Official Documentation**
- https://docs.aws.amazon.com/redshift/latest/dg/t_loading-tables-from-s3.html
- https://docs.aws.amazon.com/redshift/latest/dg/copy-parameters-authorization.html

Discussion:

Fix (patch):
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 8aee6f4c4f..f0c7fe78d4 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -8486,6 +8486,13 @@ def _parse_copy_parameters(self) -> t.List[exp.CopyParameter]:
             elif prev == "FILE_FORMAT":
                 # T-SQL's external file format case
                 param.set("expression", self._parse_field())
+            elif (
+                prev == "FORMAT"
+                and self._prev.token_type == TokenType.ALIAS
+                and self._match_texts(("AVRO", "JSON"))
+            ):
+                param.set("this", exp.var(f"FORMAT AS {self._prev.text.upper()}"))
+                param.set("expression", self._parse_field())
             else:
                 param.set("expression", self._parse_unquoted_field() or self._parse_bracket())
 
@@ -8508,7 +8515,10 @@ def _parse_credentials(self) -> t.Optional[exp.Credentials]:
         if self._match_text_seq("ENCRYPTION"):
             expr.set("encryption", self._parse_wrapped_options())
         if self._match_text_seq("IAM_ROLE"):
-            expr.set("iam_role", self._parse_field())
+            expr.set(
+                "iam_role",
+                exp.var(self._prev.text) if self._match(TokenType.DEFAULT) else self._parse_field(),
+            )
         if self._match_text_seq("REGION"):
             expr.set("region", self._parse_field())
 


Test changes:
diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py
index fbbb7d2b99..9383834721 100644
--- a/tests/dialects/test_redshift.py
+++ b/tests/dialects/test_redshift.py
@@ -347,6 +347,12 @@ def test_identity(self):
         self.validate_identity("CREATE TABLE datetable (start_date DATE, end_date DATE)")
         self.validate_identity("SELECT APPROXIMATE AS y")
         self.validate_identity("CREATE TABLE t (c BIGINT IDENTITY(0, 1))")
+        self.validate_identity(
+            "COPY test_staging_tbl FROM 's3://your/bucket/prefix/here' IAM_ROLE default FORMAT AS AVRO 'auto'"
+        )
+        self.validate_identity(
+            "COPY test_staging_tbl FROM 's3://your/bucket/prefix/here' IAM_ROLE default FORMAT AS JSON 's3://jsonpaths_file'"
+        )
         self.validate_identity(
             "SELECT * FROM venue WHERE (venuecity, venuestate) IN (('Miami', 'FL'), ('Tampa', 'FL')) ORDER BY venueid"
         )