Repository: sqlfluff/sqlfluff
Pull request: Issue 911: [BigQuery] Using functions from other projects, quoted function names
Resolves #911

This PR changes the parse tree for `function_name`, hence the large number of files changed (`.yml` files for the parse test files).

Problem statement:
[BigQuery] Using functions from other projects
Hi there!

I've tested these 2 different ways of calling _remote_ functions and both worked inside the BigQuery editor.

```sql
SELECT
    `another-gcp-project`.functions.timestamp_parsing(log_tbl.first_move) AS first_move,
    `another-gcp-project.functions.timestamp_parsing`(log_tbl.orderdate) AS orderdate
FROM
    `gcp-project.data.year_2021` AS log_tbl
;
```

## Expected Behaviour

No errors.

## Observed Behaviour

```
L:   3 | P:  27 | L026 | Reference
                       | '`another-gcp-project`.functions.timestamp_parsing'
                       | refers to table/view 'functions' not found in the FROM
                       | clause or found in parent subquery.
L:   3 | P:  54 |  PRS | Found unparsable section: '(log_tbl.first_move) AS
                       | first_move'
L:   4 | P:   5 | L026 | Reference
                       | '`another-gcp-project.functions.timestamp_parsing`'
                       | refers to table/view 'functions' not found in the FROM
                       | clause or found in parent subquery.
L:   4 | P:  54 |  PRS | Found unparsable section: '(log_tbl.orderdate) AS
                       | orderdate'
L:   6 | P:  37 | L031 | Avoid using aliases in join condition
```

## Steps to Reproduce

## Version

```
sqlfluff, version 0.4.1
```

## Configuration
```
[sqlfluff]
verbose = 0
dialect = bigquery
templater = python
exclude_rules = L009,L013

[sqlfluff:rules]
max_line_length = 120

[sqlfluff:templater:python:context]
source_table_id=my_table
start_date=456
end_date=456
fixed_carrier_id=456
fixed_destination_country_id=456
fixed_origin_country_id=456
number_of_buckets=123
bucket_id=123
fixed_carrier_ids=('1','2','3')
fixed_destination_country_ids=('1','2','3')
fixed_origin_country_ids=('1','2','3')
```

Hints from issue discussion:
Thanks, @barrachri -- I'm not surprised those are failing to parse. I'll try and look into this some time in the next few weeks. SQLFluff is definitely open to new contributors, and the parser is a common area for folks to start. If you feel like taking on this or other issues, we are here to help. It would be great to have another BigQuery user as a regular contributor. 💪🏽
I'll start looking at this today. With any luck, it'll be easy. I was delaying work on this until the PR for the similar issue #895 was merged.

Commit message: Issue 911: [BigQuery] Using functions from other projects, quoted function names

Patch to review:
diff --git a/src/sqlfluff/dialects/dialect_ansi.py b/src/sqlfluff/dialects/dialect_ansi.py
index 5d5fd1aeed4..42e023474b0 100644
--- a/src/sqlfluff/dialects/dialect_ansi.py
+++ b/src/sqlfluff/dialects/dialect_ansi.py
@@ -759,13 +759,17 @@ class FunctionSegment(BaseSegment):
     type = "function"
     match_grammar = Sequence(
         Sequence(
-            Sequence(
-                # a stored function could be accessed by schema identifier
+            # Project name, schema identifier, etc.
+            AnyNumberOf(
+                Sequence(
+                    Ref("SingleIdentifierGrammar"),
+                    Ref("DotSegment"),
+                ),
+            ),
+            OneOf(
+                Ref("FunctionNameSegment"),
                 Ref("SingleIdentifierGrammar"),
-                Ref("DotSegment"),
-                optional=True,
             ),
-            Ref("FunctionNameSegment"),
             Bracketed(
                 Ref(
                     "FunctionContentsGrammar",
diff --git a/src/sqlfluff/dialects/dialect_bigquery.py b/src/sqlfluff/dialects/dialect_bigquery.py
index 9a5b365c030..4583cf938c7 100644
--- a/src/sqlfluff/dialects/dialect_bigquery.py
+++ b/src/sqlfluff/dialects/dialect_bigquery.py
@@ -79,8 +79,8 @@
 # Unreserved Keywords
 bigquery_dialect.sets("unreserved_keywords").add("SYSTEM_TIME")
 bigquery_dialect.sets("unreserved_keywords").remove("FOR")
-bigquery_dialect.sets("unreserved_keywords").add("STRUCT")
 # Reserved Keywords
+bigquery_dialect.sets("reserved_keywords").add("STRUCT")
 bigquery_dialect.sets("reserved_keywords").add("FOR")
 
 # In BigQuery, UNNEST() returns a "value table".
diff --git a/test/fixtures/parser/ansi/double_dot.yml b/test/fixtures/parser/ansi/double_dot.yml
index ff18aa01cf3..21696bedf93 100644
--- a/test/fixtures/parser/ansi/double_dot.yml
+++ b/test/fixtures/parser/ansi/double_dot.yml
@@ -16,4 +16,4 @@ file:
               - identifier: my_database
               - dot: .
               - dot: .
-              - identifier: my_table
\ No newline at end of file
+              - identifier: my_table
diff --git a/test/fixtures/parser/ansi/transactions.yml b/test/fixtures/parser/ansi/transactions.yml
index 1196a337a6b..d73f85e0c35 100644
--- a/test/fixtures/parser/ansi/transactions.yml
+++ b/test/fixtures/parser/ansi/transactions.yml
@@ -1,31 +1,31 @@
-file:
-  - statement:
-      transaction_statement:
-      - keyword: BEGIN
-      - keyword: TRANSACTION
-  - statement_terminator: ;
-  - statement:
-      update_statement:
-        keyword: UPDATE
-        table_reference:
-          identifier: tbl
-        set_clause_list:
-          keyword: SET
-          set_clause:
-            column_reference:
-              identifier: foo
-            comparison_operator: '='
-            literal: '1'
-        where_clause:
-          keyword: WHERE
-          expression:
-            column_reference:
-              identifier: bar
-            comparison_operator: '='
-            literal: '2'
-  - statement_terminator: ;
-  - statement:
-      transaction_statement:
-      - keyword: END
-      - keyword: TRANSACTION
-  - statement_terminator: ;
\ No newline at end of file
+file:
+- statement:
+    transaction_statement:
+    - keyword: BEGIN
+    - keyword: TRANSACTION
+- statement_terminator: ;
+- statement:
+    update_statement:
+      keyword: UPDATE
+      table_reference:
+        identifier: tbl
+      set_clause_list:
+        keyword: SET
+        set_clause:
+          column_reference:
+            identifier: foo
+          comparison_operator: '='
+          literal: '1'
+      where_clause:
+        keyword: WHERE
+        expression:
+          column_reference:
+            identifier: bar
+          comparison_operator: '='
+          literal: '2'
+- statement_terminator: ;
+- statement:
+    transaction_statement:
+    - keyword: END
+    - keyword: TRANSACTION
+- statement_terminator: ;
diff --git a/test/fixtures/parser/bigquery/select_udf_quote_everything.sql b/test/fixtures/parser/bigquery/select_udf_quote_everything.sql
new file mode 100644
index 00000000000..a3d31271af2
--- /dev/null
+++ b/test/fixtures/parser/bigquery/select_udf_quote_everything.sql
@@ -0,0 +1,4 @@
+SELECT
+    `another-gcp-project.functions.timestamp_parsing`(log_tbl.orderdate) AS orderdate
+FROM
+    `gcp-project.data.year_2021` AS log_tbl
diff --git a/test/fixtures/parser/bigquery/select_udf_quote_everything.yml b/test/fixtures/parser/bigquery/select_udf_quote_everything.yml
new file mode 100644
index 00000000000..9819c4684a3
--- /dev/null
+++ b/test/fixtures/parser/bigquery/select_udf_quote_everything.yml
@@ -0,0 +1,28 @@
+file:
+  statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          function:
+            identifier: '`another-gcp-project.functions.timestamp_parsing`'
+            start_bracket: (
+            expression:
+              column_reference:
+              - identifier: log_tbl
+              - dot: .
+              - identifier: orderdate
+            end_bracket: )
+          alias_expression:
+            keyword: AS
+            identifier: orderdate
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                identifier: '`gcp-project.data.year_2021`'
+            alias_expression:
+              keyword: AS
+              identifier: log_tbl
diff --git a/test/fixtures/parser/bigquery/select_udf_quote_nothing.sql b/test/fixtures/parser/bigquery/select_udf_quote_nothing.sql
new file mode 100644
index 00000000000..a741ef4b7d9
--- /dev/null
+++ b/test/fixtures/parser/bigquery/select_udf_quote_nothing.sql
@@ -0,0 +1,6 @@
+SELECT
+    gcpproject.functions.timestamp_parsing(log_tbl.orderdate) AS orderdate
+FROM
+    `gcp-project.data.year_2021` AS log_tbl
+
+
diff --git a/test/fixtures/parser/bigquery/select_udf_quote_nothing.yml b/test/fixtures/parser/bigquery/select_udf_quote_nothing.yml
new file mode 100644
index 00000000000..10faed3d9f5
--- /dev/null
+++ b/test/fixtures/parser/bigquery/select_udf_quote_nothing.yml
@@ -0,0 +1,32 @@
+file:
+  statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          function:
+          - identifier: gcpproject
+          - dot: .
+          - identifier: functions
+          - dot: .
+          - function_name: timestamp_parsing
+          - start_bracket: (
+          - expression:
+              column_reference:
+              - identifier: log_tbl
+              - dot: .
+              - identifier: orderdate
+          - end_bracket: )
+          alias_expression:
+            keyword: AS
+            identifier: orderdate
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                identifier: '`gcp-project.data.year_2021`'
+            alias_expression:
+              keyword: AS
+              identifier: log_tbl
diff --git a/test/fixtures/parser/bigquery/select_udf_quote_project_and_datasetfunctionname.sql b/test/fixtures/parser/bigquery/select_udf_quote_project_and_datasetfunctionname.sql
new file mode 100644
index 00000000000..339b2f88a80
--- /dev/null
+++ b/test/fixtures/parser/bigquery/select_udf_quote_project_and_datasetfunctionname.sql
@@ -0,0 +1,4 @@
+SELECT
+    `another-gcp-project`.`functions.timestamp_parsing` (log_tbl.first_move) AS first_move
+FROM
+    `gcp-project.data.year_2021` AS log_tbl
diff --git a/test/fixtures/parser/bigquery/select_udf_quote_project_and_datasetfunctionname.yml b/test/fixtures/parser/bigquery/select_udf_quote_project_and_datasetfunctionname.yml
new file mode 100644
index 00000000000..7c27ceabe58
--- /dev/null
+++ b/test/fixtures/parser/bigquery/select_udf_quote_project_and_datasetfunctionname.yml
@@ -0,0 +1,30 @@
+file:
+  statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          function:
+          - identifier: '`another-gcp-project`'
+          - dot: .
+          - identifier: '`functions.timestamp_parsing`'
+          - start_bracket: (
+          - expression:
+              column_reference:
+              - identifier: log_tbl
+              - dot: .
+              - identifier: first_move
+          - end_bracket: )
+          alias_expression:
+            keyword: AS
+            identifier: first_move
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                identifier: '`gcp-project.data.year_2021`'
+            alias_expression:
+              keyword: AS
+              identifier: log_tbl
diff --git a/test/fixtures/parser/bigquery/select_udf_quote_project_name.sql b/test/fixtures/parser/bigquery/select_udf_quote_project_name.sql
new file mode 100644
index 00000000000..a9847ba87d5
--- /dev/null
+++ b/test/fixtures/parser/bigquery/select_udf_quote_project_name.sql
@@ -0,0 +1,4 @@
+SELECT
+    `another-gcp-project`.functions.timestamp_parsing(log_tbl.first_move) AS first_move
+FROM
+    `gcp-project.data.year_2021` AS log_tbl
diff --git a/test/fixtures/parser/bigquery/select_udf_quote_project_name.yml b/test/fixtures/parser/bigquery/select_udf_quote_project_name.yml
new file mode 100644
index 00000000000..b44e7499bf6
--- /dev/null
+++ b/test/fixtures/parser/bigquery/select_udf_quote_project_name.yml
@@ -0,0 +1,32 @@
+file:
+  statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          function:
+          - identifier: '`another-gcp-project`'
+          - dot: .
+          - identifier: functions
+          - dot: .
+          - function_name: timestamp_parsing
+          - start_bracket: (
+          - expression:
+              column_reference:
+              - identifier: log_tbl
+              - dot: .
+              - identifier: first_move
+          - end_bracket: )
+          alias_expression:
+            keyword: AS
+            identifier: first_move
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                identifier: '`gcp-project.data.year_2021`'
+            alias_expression:
+              keyword: AS
+              identifier: log_tbl
diff --git a/test/fixtures/parser/mysql/force_index.yml b/test/fixtures/parser/mysql/force_index.yml
index d6a86263118..5e43d8d835a 100644
--- a/test/fixtures/parser/mysql/force_index.yml
+++ b/test/fixtures/parser/mysql/force_index.yml
@@ -1,50 +1,50 @@
-file:
-  statement:
-    select_statement:
-      select_clause:
-      - keyword: SELECT
-      - select_clause_element:
-          column_reference:
-          - identifier: onetable
-          - dot: .
-          - identifier: f1
-      - comma: ','
-      - select_clause_element:
-          column_reference:
-          - identifier: twotable
-          - dot: .
-          - identifier: f1
-      from_clause:
-        keyword: FROM
-        from_expression:
-          from_expression_element:
-            table_expression:
-              table_reference:
-                identifier: onetable
-          join_clause:
-          - keyword: LEFT
-          - keyword: JOIN
-          - from_expression_element:
-            - table_expression:
-                table_reference:
-                  identifier: twotable
-            - keyword: FORCE
-            - keyword: INDEX
-            - keyword: FOR
-            - keyword: JOIN
-            - start_bracket: (
-            - object_reference:
-                identifier: idx_index
-            - end_bracket: )
-          - join_on_condition:
-              keyword: 'ON'
-              expression:
-              - column_reference:
-                - identifier: onetable
-                - dot: .
-                - identifier: f1
-              - comparison_operator: '='
-              - column_reference:
-                - identifier: twotable
-                - dot: .
-                - identifier: f1
\ No newline at end of file
+file:
+  statement:
+    select_statement:
+      select_clause:
+      - keyword: SELECT
+      - select_clause_element:
+          column_reference:
+          - identifier: onetable
+          - dot: .
+          - identifier: f1
+      - comma: ','
+      - select_clause_element:
+          column_reference:
+          - identifier: twotable
+          - dot: .
+          - identifier: f1
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                identifier: onetable
+          join_clause:
+          - keyword: LEFT
+          - keyword: JOIN
+          - from_expression_element:
+            - table_expression:
+                table_reference:
+                  identifier: twotable
+            - keyword: FORCE
+            - keyword: INDEX
+            - keyword: FOR
+            - keyword: JOIN
+            - start_bracket: (
+            - object_reference:
+                identifier: idx_index
+            - end_bracket: )
+          - join_on_condition:
+              keyword: 'ON'
+              expression:
+              - column_reference:
+                - identifier: onetable
+                - dot: .
+                - identifier: f1
+              - comparison_operator: '='
+              - column_reference:
+                - identifier: twotable
+                - dot: .
+                - identifier: f1
diff --git a/test/fixtures/parser/mysql/use_index.yml b/test/fixtures/parser/mysql/use_index.yml
index e8207b1e524..b3df274e273 100644
--- a/test/fixtures/parser/mysql/use_index.yml
+++ b/test/fixtures/parser/mysql/use_index.yml
@@ -1,30 +1,30 @@
-file:
-  statement:
-    select_statement:
-      select_clause:
-        keyword: SELECT
-        select_clause_element:
-          wildcard_expression:
-            wildcard_identifier:
-              star: '*'
-      from_clause:
-        keyword: FROM
-        from_expression:
-          from_expression_element:
-          - table_expression:
-              table_reference:
-                identifier: t1
-          - alias_expression:
-              identifier: test
-          - keyword: USE
-          - keyword: INDEX
-          - start_bracket: (
-          - object_reference:
-              identifier: i2
-          - end_bracket: )
-      orderby_clause:
-      - keyword: ORDER
-      - keyword: BY
-      - column_reference:
-          identifier: a
-  statement_terminator: ;
\ No newline at end of file
+file:
+  statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          wildcard_expression:
+            wildcard_identifier:
+              star: '*'
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+          - table_expression:
+              table_reference:
+                identifier: t1
+          - alias_expression:
+              identifier: test
+          - keyword: USE
+          - keyword: INDEX
+          - start_bracket: (
+          - object_reference:
+              identifier: i2
+          - end_bracket: )
+      orderby_clause:
+      - keyword: ORDER
+      - keyword: BY
+      - column_reference:
+          identifier: a
+  statement_terminator: ;


Code review comments:
diff_hunk: @@ -759,13 +759,17 @@ class FunctionSegment(BaseSegment):
     type = "function"
     match_grammar = Sequence(
         Sequence(
-            Sequence(
-                # a stored function could be accessed by schema identifier
+            # Project name, schema identifier, etc.
+            AnyNumberOf(
+                Sequence(
+                    Ref("SingleIdentifierGrammar"),
+                    Ref("DotSegment"),
+                ),
+            ),
+            OneOf(
+                Ref("FunctionNameSegment"),
path: src/sqlfluff/dialects/dialect_ansi.py
text: @author:
The diff is confusing here. What I've done is changed the former `Ref("FunctionNameSegment")` to:
```
            OneOf(
                Ref("FunctionNameSegment"),
                Ref("SingleIdentifierGrammar"),
            ),
```

This allows the function name to be quoted. This wasn't specifically mentioned in #911, but BigQuery does support quoting any or all parts of a function call (project, dataset, function name) together or individually.

diff_hunk: @@ -79,8 +79,8 @@
 # Unreserved Keywords
 bigquery_dialect.sets("unreserved_keywords").add("SYSTEM_TIME")
 bigquery_dialect.sets("unreserved_keywords").remove("FOR")
-bigquery_dialect.sets("unreserved_keywords").add("STRUCT")
path: src/sqlfluff/dialects/dialect_bigquery.py
text: @author:
I had to make this change to fix an existing test that was initially broken by this PR:
```
test/dialects/dialects_test.py::test__dialect__base_file_parse[bigquery-select_struct.sql]
```

diff_hunk: @@ -759,13 +759,17 @@ class FunctionSegment(BaseSegment):
     type = "function"
     match_grammar = Sequence(
         Sequence(
-            Sequence(
path: src/sqlfluff/dialects/dialect_ansi.py
text: @author:
Rather than allowing 0 or 1 names in front of the function name, allow any number. (BigQuery only uses 2, so we could limit it to 2 if that's preferable.)

@user1:
I think we should probably have `allow_gaps=False` no? Unless there can be spaces between the elements. Otherwise I wonder whether this could behave strangely.

@author:
I wasn't familiar with that property, but yes, that makes sense -- I assume it disallows white space? I'll add it.