Repository: tobymao/sqlglot
Pull request: Fix: accept some keywords as ID_VARS for BigQuery
This is a WIP because I haven't solved the issue related to the `time` identifier.

Fixes #954 

Problem statement:
Using reserved words as identifiers
In Bigquery, various reserved words and builtin function names can be used as unquoted identifiers.

E.g. `WITH view AS (SELECT 1 AS x) SELECT * FROM view` is valid in BQ but raises a ParseError "Expected CTE to have alias".  Renaming the CTE to something else stops the error.

Another example is `WITH q AS (SELECT 'A' as a, 51 as b, 23 as c UNION ALL SELECT 'B', 77, 0) SELECT * FROM q UNPIVOT(values FOR quarter IN (b, c))` which fails with ParseError "Expecting FOR" because the unpivot of `values`; another identifier parses ok.

Another example is `SELECT CAST(time*1000 AS INT64) FROM (SELECT 0.1 AS time)` where `time` is used as a column name; sqlglot raises ParseError "Expected AS after CAST".  Naming the column something else stops the error.

Hints from issue discussion:
I'll take a look. I think you just need to add the tokens of interest to `ID_VAR_TOKENS` for this to be resolved.

Commit message: Fix: accept 'view' and 'values' as valid id vars (bigquery)

Patch to review:
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index f0089e18f5..9a2554df25 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -165,6 +165,14 @@ class Parser(parser.Parser):
             TokenType.TABLE,
         }
 
+        ID_VAR_TOKENS = {
+            *parser.Parser.ID_VAR_TOKENS,  # type: ignore
+            TokenType.VALUES,
+            TokenType.VIEW,
+        }
+
+        TABLE_ALIAS_TOKENS = {*parser.Parser.TABLE_ALIAS_TOKENS} | {TokenType.VIEW}  # type: ignore
+
     class Generator(generator.Generator):
         TRANSFORMS = {
             **generator.Generator.TRANSFORMS,  # type: ignore
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index c61a2f3a8a..f1fe663c6c 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -6,6 +6,8 @@ class TestBigQuery(Validator):
     dialect = "bigquery"
 
     def test_bigquery(self):
+        self.validate_identity("WITH view AS (SELECT 1 AS x) SELECT * FROM view")
+        self.validate_identity("SELECT * FROM q UNPIVOT(values FOR quarter IN (b, c))")
         self.validate_all(
             "REGEXP_CONTAINS('foo', '.*')",
             read={"bigquery": "REGEXP_CONTAINS('foo', '.*')"},


Code review comments:
diff_hunk: @@ -165,6 +165,14 @@ class Parser(parser.Parser):
             TokenType.TABLE,
         }
 
+        ID_VAR_TOKENS = {
+            *parser.Parser.ID_VAR_TOKENS,  # type: ignore
+            TokenType.VALUES,
+            TokenType.VIEW,
path: sqlglot/dialects/bigquery.py
text: @user1:
why not put this in the base?

@author:
`VALUES` can't be put in the base parser because in some places we have e.g. `VALUES (1, 2, 3)`, so it tries to parse it as aliases and fails. We can put `VIEW` in there, though. Do you think we should make this change? I thought initially it'd be best to keep it local to bigquery.

@user1:
can other system uses view as an alia’s?

@author:
Seems like postgres and duckdb allow it. Making the change in a sec.