Repository: sqlfluff/sqlfluff
Pull request: Redefine MySQL Interval segment
<!--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 #2224 

MySQL allows more complex arguments in INTERVAL. Append a few tests to reflect this.

### Are there any other side effects of this change that we should be aware of?
No

### 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:
MySQL: Dialect cannot parse `INTERVAL -30 DAYS`
## Expected Behaviour
The following statement ought to be parsable:
```
SELECT class, AVG(pageViews), AVG(exitPageViews)
FROM pageview_table
WHERE segment_date = DATE_ADD(CURDATE(), INTERVAL -30 DAY)
GROUP BY class
```

## Observed Behaviour
The statement is not parsable and it throws the following error:
`Found unparsable section: ', | INTERVAL -30 DAY'`

## Steps to Reproduce
Lint the statement using `sqlfluff lint test.sql --dialect=mysql`

## Dialect
`mysql`

## Version
`0.9.0`

## Configuration
Default

Hints from issue discussion:

Commit message: fix #2224 by redefining interval segment for mysql

Patch to review:
diff --git a/src/sqlfluff/dialects/dialect_mysql.py b/src/sqlfluff/dialects/dialect_mysql.py
index 5180ade4e28..83a9bb385d8 100644
--- a/src/sqlfluff/dialects/dialect_mysql.py
+++ b/src/sqlfluff/dialects/dialect_mysql.py
@@ -304,6 +304,25 @@ class TableConstraintSegment(BaseSegment):
     )
 
 
+@mysql_dialect.segment(replace=True)
+class IntervalExpressionSegment(BaseSegment):
+    """An interval expression segment."""
+
+    type = "interval_expression"
+    match_grammar = Sequence(
+        "INTERVAL",
+        OneOf(
+            # The Numeric Version
+            Sequence(
+                Ref("ExpressionSegment"),
+                OneOf(Ref("QuotedLiteralSegment"), Ref("DatetimeUnitSegment")),
+            ),
+            # The String version
+            Ref("QuotedLiteralSegment"),
+        ),
+    )
+
+
 mysql_dialect.add(
     DoubleForwardSlashSegment=StringParser(
         "//", SymbolSegment, name="doubleforwardslash", type="statement_terminator"
diff --git a/test/fixtures/dialects/mysql/interval.sql b/test/fixtures/dialects/mysql/interval.sql
new file mode 100644
index 00000000000..24e76930bdb
--- /dev/null
+++ b/test/fixtures/dialects/mysql/interval.sql
@@ -0,0 +1,11 @@
+SELECT DATE_ADD(CURDATE(), INTERVAL -30 DAY);
+
+SELECT SUBDATE('2008-01-02', INTERVAL 31 DAY);
+
+SELECT DATE_ADD(CURDATE(), INTERVAL -30 DAY);
+
+SELECT DATE_ADD('1992-12-31 23:59:59.000002', INTERVAL '1.999999' SECOND_MICROSECOND);
+
+SELECT DATE_ADD('2100-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND);
+
+SELECT DATE_ADD(CURDATE(), INTERVAL 7 * 4 DAY);
diff --git a/test/fixtures/dialects/mysql/interval.yml b/test/fixtures/dialects/mysql/interval.yml
new file mode 100644
index 00000000000..f78ac42e11a
--- /dev/null
+++ b/test/fixtures/dialects/mysql/interval.yml
@@ -0,0 +1,154 @@
+# YML test files are auto-generated from SQL files and should not be edited by
+# hand. To help enforce this, the "hash" field in the file must match a hash
+# 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: 5521ee1a7aa4778686f77ce725c7f053dbabbfec9611367121e15ad970598c15
+file:
+- statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          function:
+            function_name:
+              function_name_identifier: DATE_ADD
+            bracketed:
+            - start_bracket: (
+            - expression:
+                function:
+                  function_name:
+                    function_name_identifier: CURDATE
+                  bracketed:
+                    start_bracket: (
+                    end_bracket: )
+            - comma: ','
+            - expression:
+                interval_expression:
+                  keyword: INTERVAL
+                  expression:
+                    numeric_literal:
+                      binary_operator: '-'
+                      literal: '30'
+                  date_part: DAY
+            - end_bracket: )
+- statement_terminator: ;
+- statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          function:
+            function_name:
+              function_name_identifier: SUBDATE
+            bracketed:
+            - start_bracket: (
+            - expression:
+                literal: "'2008-01-02'"
+            - comma: ','
+            - expression:
+                interval_expression:
+                  keyword: INTERVAL
+                  expression:
+                    literal: '31'
+                  date_part: DAY
+            - end_bracket: )
+- statement_terminator: ;
+- statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          function:
+            function_name:
+              function_name_identifier: DATE_ADD
+            bracketed:
+            - start_bracket: (
+            - expression:
+                function:
+                  function_name:
+                    function_name_identifier: CURDATE
+                  bracketed:
+                    start_bracket: (
+                    end_bracket: )
+            - comma: ','
+            - expression:
+                interval_expression:
+                  keyword: INTERVAL
+                  expression:
+                    numeric_literal:
+                      binary_operator: '-'
+                      literal: '30'
+                  date_part: DAY
+            - end_bracket: )
+- statement_terminator: ;
+- statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          function:
+            function_name:
+              function_name_identifier: DATE_ADD
+            bracketed:
+            - start_bracket: (
+            - expression:
+                literal: "'1992-12-31 23:59:59.000002'"
+            - comma: ','
+            - expression:
+                keyword: INTERVAL
+                literal: "'1.999999'"
+            - expression:
+                column_reference:
+                  identifier: SECOND_MICROSECOND
+            - end_bracket: )
+- statement_terminator: ;
+- statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          function:
+            function_name:
+              function_name_identifier: DATE_ADD
+            bracketed:
+            - start_bracket: (
+            - expression:
+                literal: "'2100-12-31 23:59:59'"
+            - comma: ','
+            - expression:
+                keyword: INTERVAL
+                literal: "'1:1'"
+            - expression:
+                column_reference:
+                  identifier: MINUTE_SECOND
+            - end_bracket: )
+- statement_terminator: ;
+- statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          function:
+            function_name:
+              function_name_identifier: DATE_ADD
+            bracketed:
+            - start_bracket: (
+            - expression:
+                function:
+                  function_name:
+                    function_name_identifier: CURDATE
+                  bracketed:
+                    start_bracket: (
+                    end_bracket: )
+            - comma: ','
+            - expression:
+                interval_expression:
+                  keyword: INTERVAL
+                  expression:
+                  - literal: '7'
+                  - binary_operator: '*'
+                  - literal: '4'
+                  date_part: DAY
+            - end_bracket: )
+- statement_terminator: ;


Code review comments:
diff_hunk: @@ -304,6 +304,25 @@ class TableConstraintSegment(BaseSegment):
     )
 
 
+@mysql_dialect.segment(replace=True)
+class IntervalExpressionSegment(BaseSegment):
+    """An interval expression segment."""
path: src/sqlfluff/dialects/dialect_mysql.py
text: @user1:
```suggestion
    """An interval expression segment.
    
    https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_adddate
    """
```