Repository: sqlfluff/sqlfluff
Pull request: Remove DATE, DATETIME and TIME from BigQuery DatePart
<!--Firstly, thanks for adding this feature! Secondly, please check the key steps against the checklist below to make your contribution easy to merge.-->

<!--Please give the Pull Request a meaningful title (including the dialect this PR is for if it is dialect specific), as this will automatically be added to the release notes, and then the Change Log.-->

### Brief summary of the change made
<!--If there is an open issue for this, then please include `fixes #XXXX` or `closes #XXXX` replacing `XXXX` with the issue number and it will automatically close the issue when the pull request is merged. Alternatively if not fully closed you can say `makes progress on #XXXX` to create a link on that issue without closing it.-->
Fixes #2281 

### Are there any other side effects of this change that we should be aware of?
This was added as part of #443 but can'd see why DATE and TIME were added as not part of https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#extract as referenced in the code so think they were added in error.

### Pull Request checklist
- [x] Please confirm you have completed any of the necessary steps below.

- Included test cases to demonstrate any code changes, which may be one or more of the following:
  - `.yml` rule test cases in `test/fixtures/rules/std_rule_cases`.
  - `.sql`/`.yml` parser test cases in `test/fixtures/dialects` (note YML files can be auto generated with `tox -e generate-fixture-yml`).
  - Full autofix test cases in `test/fixtures/linter/autofix`.
  - Other.
- Added appropriate documentation for the change.
- Created GitHub issues for any relevant followup/future enhancements if appropriate.


Problem statement:
L010: SQLFluff 0.9.1 capitalizes columns named date when used as parameters in DATE_ADD 
<!--If this is a parsing or linting issue, please include a minimal SQL example which reproduces the issue, along with the `sqlfluff parse` output, `sqlfluff lint` output and `sqlfluff fix` output when relevant.-->

## Expected Behaviour
When run on a SQL file containing a line like DATE_ADD(date, INTERVAL 5 YEAR), SQLFluff should not prompt or change date to be capitalized because it is a variable name, not a SQL keyword, in this context.

## Observed Behavior
Starting in SQLFluff 0.9.1, for this function (and possibly others) DATE is treated like an SQL keyword rather than a column name.

## Steps to Reproduce
(1). Generate a SQL file of the form:
```sql
SELECT DATE_ADD(date, INTERVAL 5 YEAR) AS display_date
FROM
    model
```
(2). Set BigQuery as the dialect and run sqlfluff lint on this file.

Output is **L:   1 | P:  17 | L010 | Keywords must be upper case.**

## Dialect
BigQuery

## Version
SQLFluff version is 0.9.1, testing in 0.9.0 doesn't produce the same error
Python version is 3.8.1.0.
(I'm using DBT but the same issue occurs when not using dbt as a template).

## Configuration
```
[sqlfluff]
dialect = bigquery
exclude_rules = L003,L008,L011,L014,L016,L029,L031,L034

[sqlfluff:rules]
max_line_length = 120
comma_style = leading

[sqlfluff:rules:L010]
capitalisation_policy = upper

[sqlfluff:rules:L030]
capitalisation_policy = upper
```

Hints from issue discussion:
Good spot!

I think this is a parsing issue rather than an issue with L010.

If you parse with a more obvious column name
e.g.
```sql
SELECT DATE_ADD(abc, INTERVAL 5 YEAR) AS display_date
FROM
    model
```
the function looks like:
```
[L:  1, P:  8]      |                    function:
[L:  1, P:  8]      |                        function_name:
[L:  1, P:  8]      |                            function_name_identifier:         'DATE_ADD'
[L:  1, P: 16]      |                        bracketed:
[L:  1, P: 16]      |                            start_bracket:                    '('
[L:  1, P: 17]      |                            [META] indent:
[L:  1, P: 17]      |                            expression:
[L:  1, P: 17]      |                                column_reference:
[L:  1, P: 17]      |                                    identifier:               'abc'
[L:  1, P: 20]      |                            comma:                            ','
[L:  1, P: 21]      |                            whitespace:                       ' '
[L:  1, P: 22]      |                            expression:
[L:  1, P: 22]      |                                interval_expression:
[L:  1, P: 22]      |                                    keyword:                  'INTERVAL'
[L:  1, P: 30]      |                                    whitespace:               ' '
[L:  1, P: 31]      |                                    expression:
[L:  1, P: 31]      |                                        literal:              '5'
[L:  1, P: 32]      |                                    whitespace:               ' '
[L:  1, P: 33]      |                                    date_part:                'YEAR'
[L:  1, P: 37]      |                            [META] dedent:
[L:  1, P: 37]      |                            end_bracket:                      ')'
```

whereas when you parse with the column called `date` (also `datetime`) it gives:
```
[L:  1, P:  8]      |                    function:
[L:  1, P:  8]      |                        function_name:
[L:  1, P:  8]      |                            function_name_identifier:         'DATE_ADD'
[L:  1, P: 16]      |                        bracketed:
[L:  1, P: 16]      |                            start_bracket:                    '('
[L:  1, P: 17]      |                            [META] indent:
[L:  1, P: 17]      |                            date_part:                        'date'
[L:  1, P: 21]      |                            comma:                            ','
[L:  1, P: 22]      |                            whitespace:                       ' '
[L:  1, P: 23]      |                            expression:
[L:  1, P: 23]      |                                interval_expression:
[L:  1, P: 23]      |                                    keyword:                  'INTERVAL'
[L:  1, P: 31]      |                                    whitespace:               ' '
[L:  1, P: 32]      |                                    expression:
[L:  1, P: 32]      |                                        literal:              '5'
[L:  1, P: 33]      |                                    whitespace:               ' '
[L:  1, P: 34]      |                                    date_part:                'YEAR'
[L:  1, P: 38]      |                            [META] dedent:
[L:  1, P: 38]      |                            end_bracket:                      ')'
```
So `date` is being interpreted as a `date_part` rather than a `column_reference` and therefore it gets capitalised. (Introduced in #2167).

A PR should be looking at fixing the function grammar to make `date` parse as a `column_reference` in this context


Interestingly `date` already parses as a `column_reference` is set to ANSI and therefore this error doesn't occur. So this must be bigquery specific

Commit message: Add test case

Patch to review:
diff --git a/src/sqlfluff/dialects/dialect_bigquery.py b/src/sqlfluff/dialects/dialect_bigquery.py
index d1541dff738..8bc802ef37d 100644
--- a/src/sqlfluff/dialects/dialect_bigquery.py
+++ b/src/sqlfluff/dialects/dialect_bigquery.py
@@ -209,7 +209,7 @@
 # Add additional datetime units
 # https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#extract
 bigquery_dialect.sets("datetime_units").update(
-    ["MICROSECOND", "DAYOFWEEK", "ISOWEEK", "ISOYEAR", "DATE", "DATETIME", "TIME"]
+    ["MICROSECOND", "DAYOFWEEK", "ISOWEEK", "ISOYEAR"]
 )
 
 # In BigQuery, UNNEST() returns a "value table".
diff --git a/test/fixtures/dialects/bigquery/select_datetime.yml b/test/fixtures/dialects/bigquery/select_datetime.yml
index aff1fed94f3..619a6980095 100644
--- a/test/fixtures/dialects/bigquery/select_datetime.yml
+++ b/test/fixtures/dialects/bigquery/select_datetime.yml
@@ -3,7 +3,7 @@
 # computed by SQLFluff when running the tests. Please run
 # `python test/generate_parse_fixture_yml.py`  to generate them after adding or
 # altering SQL files.
-_hash: c5d4aaf945a0b33eedca24cbf1da8067d6b561793567c998626d0d1c98464f76
+_hash: 856e7567c910edc7e3f6a24382c331edcf95beb91f095fd138e4b5d8faaaab7a
 file:
   statement:
     select_statement:
@@ -26,15 +26,17 @@ file:
             function_name:
               function_name_identifier: EXTRACT
             bracketed:
-              start_bracket: (
-              date_part: DATE
-              keyword: FROM
-              expression:
+            - start_bracket: (
+            - expression:
+                column_reference:
+                  identifier: DATE
+            - keyword: FROM
+            - expression:
                 column_reference:
                 - identifier: gmv
                 - dot: .
                 - identifier: order_created_at
-              end_bracket: )
+            - end_bracket: )
           alias_expression:
             keyword: AS
             identifier: order_dt
diff --git a/test/fixtures/rules/std_rule_cases/L010.yml b/test/fixtures/rules/std_rule_cases/L010.yml
index efa9dba859c..1b493f364b7 100644
--- a/test/fixtures/rules/std_rule_cases/L010.yml
+++ b/test/fixtures/rules/std_rule_cases/L010.yml
@@ -146,3 +146,12 @@ test_fail_data_type_capitalisation_policy_keywords_2:
     rules:
       L010:
         capitalisation_policy: upper
+
+test_pass_bigquery_date:
+  pass_str: SELECT DATE_ADD(date, INTERVAL 5 YEAR) AS display_date
+  configs:
+    core:
+      dialect: bigquery
+    rules:
+      L010:
+        capitalisation_policy: upper


Code review comments:
diff_hunk: @@ -26,15 +26,17 @@ file:
             function_name:
               function_name_identifier: EXTRACT
             bracketed:
-              start_bracket: (
-              date_part: DATE
-              keyword: FROM
-              expression:
+            - start_bracket: (
+            - expression:
+                column_reference:
+                  identifier: DATE
+            - keyword: FROM
+            - expression:
path: test/fixtures/dialects/bigquery/select_datetime.yml
text: @user1:
@author FYI I originally tried this same method but didn't submit because of this.

In this context in `EXTRACT` `DATE` is a vaild `date_part` (or maybe a `keyword`, don't know strictly which as the original approach may have been a bit of a hack) and should therefore be controlled by L010.
`EXTRACT(DATE FROM gmv.order_created_at)` converts the datetime value in the column into a `DATE`. So making this change would just introduce the opposite bug into `DATE` from `EXTRACT`.

Feels like the current `FunctionContentsGrammar` approach is too general to be applied to all functions. Interestingly in [ANSI](https://github.com/sqlfluff/sqlfluff/blob/80be738545d39e578c830b4347af3f44797d3833/src/sqlfluff/dialects/dialect_ansi.py#L3363-L3372) the `DATEADD` function seems to be separated out from the other functions but this is overwritten in bigquery.

@author:
Actually this is not valid BigQuery syntax!

![image](https://user-images.githubusercontent.com/10931297/148964946-d135c762-717e-4c30-8872-aff3c43606c4.png)

Will update the date to do a real datepart.



@author:
Updated the test, and commented on the original issue that requested this to be added: https://github.com/sqlfluff/sqlfluff/issues/387#issuecomment-1010041783

@user1:
Cool thanks, let's see what the OP says.