Repository: tobymao/sqlglot

Issue:
Parse error for BigQuery when `values` column referenced in `STRUCT()` expression
In BigQuery `VALUES` isn't a reserved keyword and can be used as a column name.  However, when sqlglot parses a query where a `values` column is referenced in a `STRUCT()` expression it raises an error.

It looks like the `values` column reference is being misinterpreted as a keyword in that case.  It appears this was also true in previous versions of sqlglot, but it simply resulted in an incorrect AST and didn't fail with an error (we noticed this issue when upgrading sqlglot from 20.4.0 to 21.1.0).  I'm guessing the change in behavior is due to #2650.

Ideally sqlglot would not treat the `values` column reference as a keyword in such cases.

**Fully reproducible code snippet**
```python
sql = '''WITH test AS (SELECT 'abc' AS values) SELECT STRUCT(values AS v) FROM test'''
sqlglot.parse(sql, read='bigquery')
```
Results in the following error:
```
sqlglot.errors.ParseError: Required keyword: 'this' missing for <class 'sqlglot.expressions.Alias'>. Line 1, Col: 64.
```

**Official Documentation**
- https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#reserved_keywords
- https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#constructing_a_struct


Discussion:

Patch:
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index 509b902a..c0191b2e 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -331,6 +331,7 @@ class BigQuery(Dialect):
             "TIMESTAMP": TokenType.TIMESTAMPTZ,
         }
         KEYWORDS.pop("DIV")
+        KEYWORDS.pop("VALUES")
 
     class Parser(parser.Parser):
         PREFIXED_PIVOT_COLUMNS = True
@@ -409,11 +410,6 @@ class BigQuery(Dialect):
             TokenType.TABLE,
         }
 
-        ID_VAR_TOKENS = {
-            *parser.Parser.ID_VAR_TOKENS,
-            TokenType.VALUES,
-        }
-
         PROPERTY_PARSERS = {
             **parser.Parser.PROPERTY_PARSERS,
             "NOT DETERMINISTIC": lambda self: self.expression(
diff --git a/sqlglot/dialects/redshift.py b/sqlglot/dialects/redshift.py
index a845d63a..135ffc6a 100644
--- a/sqlglot/dialects/redshift.py
+++ b/sqlglot/dialects/redshift.py
@@ -47,11 +47,6 @@ class Redshift(Postgres):
     }
 
     class Parser(Postgres.Parser):
-        ID_VAR_TOKENS = {
-            *Postgres.Parser.ID_VAR_TOKENS,
-            TokenType.VALUES,
-        }
-
         FUNCTIONS = {
             **Postgres.Parser.FUNCTIONS,
             "ADD_MONTHS": lambda args: exp.TsOrDsAdd(
@@ -163,6 +158,7 @@ class Redshift(Postgres):
             "UNLOAD": TokenType.COMMAND,
             "VARBYTE": TokenType.VARBINARY,
         }
+        KEYWORDS.pop("VALUES")
 
         # Redshift allows # to appear as a table identifier prefix
         SINGLE_TOKENS = Postgres.Tokenizer.SINGLE_TOKENS.copy()
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index f74e7804..ef00572b 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -2064,7 +2064,7 @@ class Parser(metaclass=_Parser):
             partition=self._parse_partition(),
             where=self._match_pair(TokenType.REPLACE, TokenType.WHERE)
             and self._parse_conjunction(),
-            expression=self._parse_ddl_select(),
+            expression=self._parse_derived_table_values() or self._parse_ddl_select(),
             conflict=self._parse_on_conflict(),
             returning=returning or self._parse_returning(),
             overwrite=overwrite,
@@ -2372,12 +2372,8 @@ class Parser(metaclass=_Parser):
             # We return early here so that the UNION isn't attached to the subquery by the
             # following call to _parse_set_operations, but instead becomes the parent node
             return self._parse_subquery(this, parse_alias=parse_subquery_alias)
-        elif self._match(TokenType.VALUES):
-            this = self.expression(
-                exp.Values,
-                expressions=self._parse_csv(self._parse_value),
-                alias=self._parse_table_alias(),
-            )
+        elif self._match(TokenType.VALUES, advance=False):
+            this = self._parse_derived_table_values()
         elif from_:
             this = exp.select("*").from_(from_.this, copy=False)
         else:
@@ -2974,7 +2970,7 @@ class Parser(metaclass=_Parser):
 
     def _parse_derived_table_values(self) -> t.Optional[exp.Values]:
         is_derived = self._match_pair(TokenType.L_PAREN, TokenType.VALUES)
-        if not is_derived and not self._match(TokenType.VALUES):
+        if not is_derived and not self._match_text_seq("VALUES"):
             return None
 
         expressions = self._parse_csv(self._parse_value)
@@ -5522,7 +5518,7 @@ class Parser(metaclass=_Parser):
                     then = self.expression(
                         exp.Insert,
                         this=self._parse_value(),
-                        expression=self._match(TokenType.VALUES) and self._parse_value(),
+                        expression=self._match_text_seq("VALUES") and self._parse_value(),
                     )
             elif self._match(TokenType.UPDATE):
                 expressions = self._parse_star()


Test patch:
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 0d462063..f2311795 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -18,86 +18,6 @@ class TestBigQuery(Validator):
     maxDiff = None
 
     def test_bigquery(self):
-        self.validate_identity("ARRAY_AGG(x IGNORE NULLS LIMIT 1)")
-        self.validate_identity("ARRAY_AGG(x IGNORE NULLS ORDER BY x LIMIT 1)")
-        self.validate_identity("ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY x LIMIT 1)")
-        self.validate_identity("ARRAY_AGG(x IGNORE NULLS)")
-        self.validate_identity("ARRAY_AGG(DISTINCT x IGNORE NULLS HAVING MAX x ORDER BY x LIMIT 1)")
-
-        self.validate_all(
-            "TIMESTAMP(x)",
-            write={
-                "bigquery": "TIMESTAMP(x)",
-                "duckdb": "CAST(x AS TIMESTAMPTZ)",
-                "presto": "CAST(x AS TIMESTAMP WITH TIME ZONE)",
-            },
-        )
-        self.validate_all(
-            "SELECT SUM(x IGNORE NULLS) AS x",
-            read={
-                "bigquery": "SELECT SUM(x IGNORE NULLS) AS x",
-                "duckdb": "SELECT SUM(x IGNORE NULLS) AS x",
-                "postgres": "SELECT SUM(x) IGNORE NULLS AS x",
-                "spark": "SELECT SUM(x) IGNORE NULLS AS x",
-                "snowflake": "SELECT SUM(x) IGNORE NULLS AS x",
-            },
-            write={
-                "bigquery": "SELECT SUM(x IGNORE NULLS) AS x",
-                "duckdb": "SELECT SUM(x IGNORE NULLS) AS x",
-                "postgres": "SELECT SUM(x) IGNORE NULLS AS x",
-                "spark": "SELECT SUM(x) IGNORE NULLS AS x",
-                "snowflake": "SELECT SUM(x) IGNORE NULLS AS x",
-            },
-        )
-        self.validate_all(
-            "SELECT SUM(x RESPECT NULLS) AS x",
-            read={
-                "bigquery": "SELECT SUM(x RESPECT NULLS) AS x",
-                "duckdb": "SELECT SUM(x RESPECT NULLS) AS x",
-                "postgres": "SELECT SUM(x) RESPECT NULLS AS x",
-                "spark": "SELECT SUM(x) RESPECT NULLS AS x",
-                "snowflake": "SELECT SUM(x) RESPECT NULLS AS x",
-            },
-            write={
-                "bigquery": "SELECT SUM(x RESPECT NULLS) AS x",
-                "duckdb": "SELECT SUM(x RESPECT NULLS) AS x",
-                "postgres": "SELECT SUM(x) RESPECT NULLS AS x",
-                "spark": "SELECT SUM(x) RESPECT NULLS AS x",
-                "snowflake": "SELECT SUM(x) RESPECT NULLS AS x",
-            },
-        )
-        self.validate_all(
-            "SELECT PERCENTILE_CONT(x, 0.5 RESPECT NULLS) OVER ()",
-            write={
-                "bigquery": "SELECT PERCENTILE_CONT(x, 0.5 RESPECT NULLS) OVER ()",
-                "duckdb": "SELECT QUANTILE_CONT(x, 0.5 RESPECT NULLS) OVER ()",
-                "spark": "SELECT PERCENTILE_CONT(x, 0.5) RESPECT NULLS OVER ()",
-            },
-        )
-        self.validate_all(
-            "SELECT ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY a, b DESC LIMIT 10) AS x",
-            write={
-                "bigquery": "SELECT ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY a, b DESC LIMIT 10) AS x",
-                "duckdb": "SELECT ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY a NULLS FIRST, b DESC LIMIT 10) AS x",
-                "spark": "SELECT COLLECT_LIST(DISTINCT x ORDER BY a, b DESC LIMIT 10) IGNORE NULLS AS x",
-            },
-        )
-        self.validate_all(
-            "SELECT ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY a, b DESC LIMIT 1, 10) AS x",
-            write={
-                "bigquery": "SELECT ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY a, b DESC LIMIT 1, 10) AS x",
-                "duckdb": "SELECT ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY a NULLS FIRST, b DESC LIMIT 1, 10) AS x",
-                "spark": "SELECT COLLECT_LIST(DISTINCT x ORDER BY a, b DESC LIMIT 1, 10) IGNORE NULLS AS x",
-            },
-        )
-        self.validate_identity("SELECT COUNT(x RESPECT NULLS)")
-        self.validate_identity("SELECT LAST_VALUE(x IGNORE NULLS) OVER y AS x")
-
-        self.validate_identity(
-            "create or replace view test (tenant_id OPTIONS(description='Test description on table creation')) select 1 as tenant_id, 1 as customer_id;",
-            "CREATE OR REPLACE VIEW test (tenant_id OPTIONS (description='Test description on table creation')) AS SELECT 1 AS tenant_id, 1 AS customer_id",
-        )
-
         with self.assertLogs(helper_logger) as cm:
             statements = parse(
                 """
@@ -139,19 +59,12 @@ class TestBigQuery(Validator):
             self.validate_all(
                 "a[0]",
                 read={
+                    "bigquery": "a[0]",
                     "duckdb": "a[1]",
                     "presto": "a[1]",
                 },
             )
 
-        self.validate_identity(
-            "select array_contains([1, 2, 3], 1)",
-            "SELECT EXISTS(SELECT 1 FROM UNNEST([1, 2, 3]) AS _col WHERE _col = 1)",
-        )
-        self.validate_identity("CREATE SCHEMA x DEFAULT COLLATE 'en'")
-        self.validate_identity("CREATE TABLE x (y INT64) DEFAULT COLLATE 'en'")
-        self.validate_identity("PARSE_JSON('{}', wide_number_mode => 'exact')")
-
         with self.assertRaises(TokenError):
             transpile("'\\'", read="bigquery")
 
@@ -187,6 +100,16 @@ class TestBigQuery(Validator):
             )
             assert "'END FOR'" in cm.output[0]
 
+        self.validate_identity("CREATE SCHEMA x DEFAULT COLLATE 'en'")
+        self.validate_identity("CREATE TABLE x (y INT64) DEFAULT COLLATE 'en'")
+        self.validate_identity("PARSE_JSON('{}', wide_number_mode => 'exact')")
+        self.validate_identity("FOO(values)")
+        self.validate_identity("STRUCT(values AS value)")
+        self.validate_identity("ARRAY_AGG(x IGNORE NULLS LIMIT 1)")
+        self.validate_identity("ARRAY_AGG(x IGNORE NULLS ORDER BY x LIMIT 1)")
+        self.validate_identity("ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY x LIMIT 1)")
+        self.validate_identity("ARRAY_AGG(x IGNORE NULLS)")
+        self.validate_identity("ARRAY_AGG(DISTINCT x IGNORE NULLS HAVING MAX x ORDER BY x LIMIT 1)")
         self.validate_identity("SELECT * FROM dataset.my_table TABLESAMPLE SYSTEM (10 PERCENT)")
         self.validate_identity("TIME('2008-12-25 15:30:00+08')")
         self.validate_identity("TIME('2008-12-25 15:30:00+08', 'America/Los_Angeles')")
@@ -245,6 +168,13 @@ class TestBigQuery(Validator):
         self.validate_identity("SELECT TIMESTAMP_SECONDS(2) AS t")
         self.validate_identity("SELECT TIMESTAMP_MILLIS(2) AS t")
         self.validate_identity("""SELECT JSON_EXTRACT_SCALAR('{"a": 5}', '$.a')""")
+        self.validate_identity("UPDATE x SET y = NULL")
+        self.validate_identity("LOG(n, b)")
+        self.validate_identity("SELECT COUNT(x RESPECT NULLS)")
+        self.validate_identity("SELECT LAST_VALUE(x IGNORE NULLS) OVER y AS x")
+        self.validate_identity(
+            "SELECT * FROM test QUALIFY a IS DISTINCT FROM b WINDOW c AS (PARTITION BY d)"
+        )
         self.validate_identity(
             "FOR record IN (SELECT word, word_count FROM bigquery-public-data.samples.shakespeare LIMIT 5) DO SELECT record.word, record.word_count"
         )
@@ -272,6 +202,14 @@ class TestBigQuery(Validator):
         self.validate_identity(
             """SELECT JSON_EXTRACT_SCALAR('5')""", """SELECT JSON_EXTRACT_SCALAR('5', '$')"""
         )
+        self.validate_identity(
+            "select array_contains([1, 2, 3], 1)",
+            "SELECT EXISTS(SELECT 1 FROM UNNEST([1, 2, 3]) AS _col WHERE _col = 1)",
+        )
+        self.validate_identity(
+            "create or replace view test (tenant_id OPTIONS(description='Test description on table creation')) select 1 as tenant_id, 1 as customer_id;",
+            "CREATE OR REPLACE VIEW test (tenant_id OPTIONS (description='Test description on table creation')) AS SELECT 1 AS tenant_id, 1 AS customer_id",
+        )
         self.validate_identity(
             "SELECT SPLIT(foo)",
             "SELECT SPLIT(foo, ',')",
@@ -320,7 +258,81 @@ class TestBigQuery(Validator):
             "SELECT * FROM UNNEST(x) WITH OFFSET EXCEPT DISTINCT SELECT * FROM UNNEST(y) WITH OFFSET",
             "SELECT * FROM UNNEST(x) WITH OFFSET AS offset EXCEPT DISTINCT SELECT * FROM UNNEST(y) WITH OFFSET AS offset",
         )
+        self.validate_identity(
+            "SELECT * FROM (SELECT a, b, c FROM test) PIVOT(SUM(b) d, COUNT(*) e FOR c IN ('x', 'y'))",
+            "SELECT * FROM (SELECT a, b, c FROM test) PIVOT(SUM(b) AS d, COUNT(*) AS e FOR c IN ('x', 'y'))",
+        )
+        self.validate_identity(
+            r"REGEXP_EXTRACT(svc_plugin_output, r'\\\((.*)')",
+            r"REGEXP_EXTRACT(svc_plugin_output, '\\\\\\((.*)')",
+        )
 
+        self.validate_all(
+            "TIMESTAMP(x)",
+            write={
+                "bigquery": "TIMESTAMP(x)",
+                "duckdb": "CAST(x AS TIMESTAMPTZ)",
+                "presto": "CAST(x AS TIMESTAMP WITH TIME ZONE)",
+            },
+        )
+        self.validate_all(
+            "SELECT SUM(x IGNORE NULLS) AS x",
+            read={
+                "bigquery": "SELECT SUM(x IGNORE NULLS) AS x",
+                "duckdb": "SELECT SUM(x IGNORE NULLS) AS x",
+                "postgres": "SELECT SUM(x) IGNORE NULLS AS x",
+                "spark": "SELECT SUM(x) IGNORE NULLS AS x",
+                "snowflake": "SELECT SUM(x) IGNORE NULLS AS x",
+            },
+            write={
+                "bigquery": "SELECT SUM(x IGNORE NULLS) AS x",
+                "duckdb": "SELECT SUM(x IGNORE NULLS) AS x",
+                "postgres": "SELECT SUM(x) IGNORE NULLS AS x",
+                "spark": "SELECT SUM(x) IGNORE NULLS AS x",
+                "snowflake": "SELECT SUM(x) IGNORE NULLS AS x",
+            },
+        )
+        self.validate_all(
+            "SELECT SUM(x RESPECT NULLS) AS x",
+            read={
+                "bigquery": "SELECT SUM(x RESPECT NULLS) AS x",
+                "duckdb": "SELECT SUM(x RESPECT NULLS) AS x",
+                "postgres": "SELECT SUM(x) RESPECT NULLS AS x",
+                "spark": "SELECT SUM(x) RESPECT NULLS AS x",
+                "snowflake": "SELECT SUM(x) RESPECT NULLS AS x",
+            },
+            write={
+                "bigquery": "SELECT SUM(x RESPECT NULLS) AS x",
+                "duckdb": "SELECT SUM(x RESPECT NULLS) AS x",
+                "postgres": "SELECT SUM(x) RESPECT NULLS AS x",
+                "spark": "SELECT SUM(x) RESPECT NULLS AS x",
+                "snowflake": "SELECT SUM(x) RESPECT NULLS AS x",
+            },
+        )
+        self.validate_all(
+            "SELECT PERCENTILE_CONT(x, 0.5 RESPECT NULLS) OVER ()",
+            write={
+                "bigquery": "SELECT PERCENTILE_CONT(x, 0.5 RESPECT NULLS) OVER ()",
+                "duckdb": "SELECT QUANTILE_CONT(x, 0.5 RESPECT NULLS) OVER ()",
+                "spark": "SELECT PERCENTILE_CONT(x, 0.5) RESPECT NULLS OVER ()",
+            },
+        )
+        self.validate_all(
+            "SELECT ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY a, b DESC LIMIT 10) AS x",
+            write={
+                "bigquery": "SELECT ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY a, b DESC LIMIT 10) AS x",
+                "duckdb": "SELECT ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY a NULLS FIRST, b DESC LIMIT 10) AS x",
+                "spark": "SELECT COLLECT_LIST(DISTINCT x ORDER BY a, b DESC LIMIT 10) IGNORE NULLS AS x",
+            },
+        )
+        self.validate_all(
+            "SELECT ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY a, b DESC LIMIT 1, 10) AS x",
+            write={
+                "bigquery": "SELECT ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY a, b DESC LIMIT 1, 10) AS x",
+                "duckdb": "SELECT ARRAY_AGG(DISTINCT x IGNORE NULLS ORDER BY a NULLS FIRST, b DESC LIMIT 1, 10) AS x",
+                "spark": "SELECT COLLECT_LIST(DISTINCT x ORDER BY a, b DESC LIMIT 1, 10) IGNORE NULLS AS x",
+            },
+        )
         self.validate_all(
             "SELECT * FROM Produce UNPIVOT((first_half_sales, second_half_sales) FOR semesters IN ((Q1, Q2) AS 'semester_1', (Q3, Q4) AS 'semester_2'))",
             read={
@@ -472,7 +484,6 @@ class TestBigQuery(Validator):
                 "duckdb": "SELECT * FROM t WHERE EXISTS(SELECT * FROM UNNEST(nums) AS _t(x) WHERE x > 1)",
             },
         )
-        self.validate_identity("UPDATE x SET y = NULL")
         self.validate_all(
             "NULL",
             read={
@@ -676,10 +687,6 @@ class TestBigQuery(Validator):
                 "bigquery": "SELECT ARRAY(SELECT AS STRUCT 1 AS a, 2 AS b)",
             },
         )
-        self.validate_identity(
-            r"REGEXP_EXTRACT(svc_plugin_output, r'\\\((.*)')",
-            r"REGEXP_EXTRACT(svc_plugin_output, '\\\\\\((.*)')",
-        )
         self.validate_all(
             "REGEXP_CONTAINS('foo', '.*')",
             read={
@@ -1002,9 +1009,6 @@ class TestBigQuery(Validator):
                 "postgres": "CURRENT_DATE AT TIME ZONE 'UTC'",
             },
         )
-        self.validate_identity(
-            "SELECT * FROM test QUALIFY a IS DISTINCT FROM b WINDOW c AS (PARTITION BY d)"
-        )
         self.validate_all(
             "SELECT a FROM test WHERE a = 1 GROUP BY a HAVING a = 2 QUALIFY z ORDER BY a LIMIT 10",
             write={
@@ -1013,45 +1017,20 @@ class TestBigQuery(Validator):
             },
         )
         self.validate_all(
-            "SELECT cola, colb FROM (VALUES (1, 'test')) AS tab(cola, colb)",
-            write={
-                "spark": "SELECT cola, colb FROM VALUES (1, 'test') AS tab(cola, colb)",
+            "SELECT cola, colb FROM UNNEST([STRUCT(1 AS cola, 'test' AS colb)])",
+            read={
                 "bigquery": "SELECT cola, colb FROM UNNEST([STRUCT(1 AS cola, 'test' AS colb)])",
                 "snowflake": "SELECT cola, colb FROM (VALUES (1, 'test')) AS tab(cola, colb)",
-            },
-        )
-        self.validate_all(
-            "SELECT cola, colb FROM (VALUES (1, 'test')) AS tab",
-            write={
-                "bigquery": "SELECT cola, colb FROM UNNEST([STRUCT(1 AS _c0, 'test' AS _c1)])",
-            },
-        )
-        self.validate_all(
-            "SELECT cola, colb FROM (VALUES (1, 'test'))",
-            write={
-                "bigquery": "SELECT cola, colb FROM UNNEST([STRUCT(1 AS _c0, 'test' AS _c1)])",
+                "spark": "SELECT cola, colb FROM VALUES (1, 'test') AS tab(cola, colb)",
             },
         )
         self.validate_all(
             "SELECT * FROM UNNEST([STRUCT(1 AS id)]) CROSS JOIN UNNEST([STRUCT(1 AS id)])",
             read={
+                "bigquery": "SELECT * FROM UNNEST([STRUCT(1 AS id)]) CROSS JOIN UNNEST([STRUCT(1 AS id)])",
                 "postgres": "SELECT * FROM (VALUES (1)) AS t1(id) CROSS JOIN (VALUES (1)) AS t2(id)",
             },
         )
-        self.validate_all(
-            "SELECT cola, colb, colc FROM (VALUES (1, 'test', NULL)) AS tab(cola, colb, colc)",
-            write={
-                "spark": "SELECT cola, colb, colc FROM VALUES (1, 'test', NULL) AS tab(cola, colb, colc)",
-                "bigquery": "SELECT cola, colb, colc FROM UNNEST([STRUCT(1 AS cola, 'test' AS colb, NULL AS colc)])",
-                "snowflake": "SELECT cola, colb, colc FROM (VALUES (1, 'test', NULL)) AS tab(cola, colb, colc)",
-            },
-        )
-        self.validate_all(
-            "SELECT * FROM (SELECT a, b, c FROM test) PIVOT(SUM(b) d, COUNT(*) e FOR c IN ('x', 'y'))",
-            write={
-                "bigquery": "SELECT * FROM (SELECT a, b, c FROM test) PIVOT(SUM(b) AS d, COUNT(*) AS e FOR c IN ('x', 'y'))",
-            },
-        )
         self.validate_all(
             "SELECT REGEXP_EXTRACT(abc, 'pattern(group)') FROM table",
             write={
@@ -1107,8 +1086,6 @@ WHERE
             pretty=True,
         )
 
-        self.validate_identity("LOG(n, b)")
-
     def test_user_defined_functions(self):
         self.validate_identity(
             "CREATE TEMPORARY FUNCTION a(x FLOAT64, y FLOAT64) RETURNS FLOAT64 NOT DETERMINISTIC LANGUAGE js AS 'return x*y;'"
@@ -1130,35 +1107,22 @@ WHERE
         )
 
     def test_remove_precision_parameterized_types(self):
-        self.validate_all(
-            "SELECT CAST(1 AS NUMERIC(10, 2))",
-            write={
-                "bigquery": "SELECT CAST(1 AS NUMERIC)",
-            },
+        self.validate_identity("CREATE TABLE test (a NUMERIC(10, 2))")
+        self.validate_identity(
+            "INSERT INTO test (cola, colb) VALUES (CAST(7 AS STRING(10)), CAST(14 AS STRING(10)))",
+            "INSERT INTO test (cola, colb) VALUES (CAST(7 AS STRING), CAST(14 AS STRING))",
         )
-        self.validate_all(
-            "CREATE TABLE test (a NUMERIC(10, 2))",
-            write={
-                "bigquery": "CREATE TABLE test (a NUMERIC(10, 2))",
-            },
+        self.validate_identity(
+            "SELECT CAST(1 AS NUMERIC(10, 2))",
+            "SELECT CAST(1 AS NUMERIC)",
         )
-        self.validate_all(
+        self.validate_identity(
             "SELECT CAST('1' AS STRING(10)) UNION ALL SELECT CAST('2' AS STRING(10))",
-            write={
-                "bigquery": "SELECT CAST('1' AS STRING) UNION ALL SELECT CAST('2' AS STRING)",
-            },
+            "SELECT CAST('1' AS STRING) UNION ALL SELECT CAST('2' AS STRING)",
         )
-        self.validate_all(
+        self.validate_identity(
             "SELECT cola FROM (SELECT CAST('1' AS STRING(10)) AS cola UNION ALL SELECT CAST('2' AS STRING(10)) AS cola)",
-            write={
-                "bigquery": "SELECT cola FROM (SELECT CAST('1' AS STRING) AS cola UNION ALL SELECT CAST('2' AS STRING) AS cola)",
-            },
-        )
-        self.validate_all(
-            "INSERT INTO test (cola, colb) VALUES (CAST(7 AS STRING(10)), CAST(14 AS STRING(10)))",
-            write={
-                "bigquery": "INSERT INTO test (cola, colb) VALUES (CAST(7 AS STRING), CAST(14 AS STRING))",
-            },
+            "SELECT cola FROM (SELECT CAST('1' AS STRING) AS cola UNION ALL SELECT CAST('2' AS STRING) AS cola)",
         )
 
     def test_models(self):
diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py
index 6925a64b..33cfa0c1 100644
--- a/tests/dialects/test_redshift.py
+++ b/tests/dialects/test_redshift.py
@@ -406,8 +406,9 @@ ORDER BY
         union_query = f"SELECT * FROM ({' UNION ALL '.join('SELECT ' + v for v in values)})"
         self.assertEqual(transpile(values_query, write="redshift")[0], union_query)
 
-        self.validate_identity(
-            "SELECT * FROM (VALUES (1), (2))",
+        values_sql = transpile("SELECT * FROM (VALUES (1), (2))", write="redshift", pretty=True)[0]
+        self.assertEqual(
+            values_sql,
             """SELECT
   *
 FROM (
@@ -417,69 +418,51 @@ FROM (
   SELECT
     2
 )""",
-            pretty=True,
         )
 
+        self.validate_identity("INSERT INTO t (a) VALUES (1), (2), (3)")
+        self.validate_identity("INSERT INTO t (a, b) VALUES (1, 2), (3, 4)")
+
         self.validate_all(
-            "SELECT * FROM (VALUES (1, 2)) AS t",
-            write={
-                "redshift": "SELECT * FROM (SELECT 1, 2) AS t",
-                "mysql": "SELECT * FROM (SELECT 1, 2) AS t",
-                "presto": "SELECT * FROM (VALUES (1, 2)) AS t",
-            },
-        )
-        self.validate_all(
-            "SELECT * FROM (VALUES (1)) AS t1(id) CROSS JOIN (VALUES (1)) AS t2(id)",
-            write={
-                "redshift": "SELECT * FROM (SELECT 1 AS id) AS t1 CROSS JOIN (SELECT 1 AS id) AS t2",
+            "SELECT * FROM (SELECT 1, 2) AS t",
+            read={
+                "": "SELECT * FROM (VALUES (1, 2)) AS t",
             },
-        )
-        self.validate_all(
-            "SELECT a, b FROM (VALUES (1, 2)) AS t (a, b)",
             write={
-                "redshift": "SELECT a, b FROM (SELECT 1 AS a, 2 AS b) AS t",
+                "mysql": "SELECT * FROM (SELECT 1, 2) AS t",
+                "presto": "SELECT * FROM (SELECT 1, 2) AS t",
             },
         )
         self.validate_all(
-            'SELECT a, b FROM (VALUES (1, 2), (3, 4)) AS "t" (a, b)',
-            write={
-                "redshift": 'SELECT a, b FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3, 4) AS "t"',
+            "SELECT * FROM (SELECT 1 AS id) AS t1 CROSS JOIN (SELECT 1 AS id) AS t2",
+            read={
+                "": "SELECT * FROM (VALUES (1)) AS t1(id) CROSS JOIN (VALUES (1)) AS t2(id)",
             },
         )
         self.validate_all(
-            "SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8)) AS t (a, b)",
-            write={
-                "redshift": "SELECT a, b FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3, 4 UNION ALL SELECT 5, 6 UNION ALL SELECT 7, 8) AS t",
+            "SELECT a, b FROM (SELECT 1 AS a, 2 AS b) AS t",
+            read={
+                "": "SELECT a, b FROM (VALUES (1, 2)) AS t (a, b)",
             },
         )
         self.validate_all(
-            "INSERT INTO t(a) VALUES (1), (2), (3)",
-            write={
-                "redshift": "INSERT INTO t (a) VALUES (1), (2), (3)",
+            'SELECT a, b FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3, 4) AS "t"',
+            read={
+                "": 'SELECT a, b FROM (VALUES (1, 2), (3, 4)) AS "t" (a, b)',
             },
         )
         self.validate_all(
-            "INSERT INTO t(a, b) SELECT a, b FROM (VALUES (1, 2), (3, 4)) AS t (a, b)",
-            write={
-                "redshift": "INSERT INTO t (a, b) SELECT a, b FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3, 4) AS t",
+            "SELECT a, b FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3, 4 UNION ALL SELECT 5, 6 UNION ALL SELECT 7, 8) AS t",
+            read={
+                "": "SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8)) AS t (a, b)",
             },
         )
         self.validate_all(
-            "INSERT INTO t(a, b) VALUES (1, 2), (3, 4)",
-            write={
-                "redshift": "INSERT INTO t (a, b) VALUES (1, 2), (3, 4)",
+            "INSERT INTO t (a, b) SELECT a, b FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3, 4) AS t",
+            read={
+                "": "INSERT INTO t(a, b) SELECT a, b FROM (VALUES (1, 2), (3, 4)) AS t (a, b)",
             },
         )
-        self.validate_identity(
-            'SELECT * FROM (VALUES (1)) AS "t"(a)',
-            '''SELECT
-  *
-FROM (
-  SELECT
-    1 AS a
-) AS "t"''',
-            pretty=True,
-        )
 
     def test_create_table_like(self):
         self.validate_identity(