Repository: sqlfluff/sqlfluff
Pull request: Postgres enhanced `DELETE FROM` syntax
<!--Thanks for adding this feature!-->

<!--Please give the Pull Request a meaningful title for the release notes-->

### Brief summary of the change made
<!--Please include `fixes #XXXX` to automatically close any corresponding issue when the pull request is merged. Alternatively if not fully closed you can say `makes progress on #XXXX`.-->
Fixes #2638 

### Are there any other side effects of this change that we should be aware of?
Note I've left some syntax as TODO as not well defined in Postgres docs with no Examples so will leave to someone with Postgres access to define later.

### 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:
[POSTGRES] Can't parse DELETE FROM [...] USING [...] WHERE 

## Expected Behaviour

Query should be parsed without error

## Observed Behaviour

Query can't be parsed

## Steps to Reproduce

Run this minimal example with sqlfluff parse/lint:

```pgsql
DELETE FROM some_table
USING other_table
WHERE other_table.col = some_table.col
```


- `sqlfluff parse` output:

```
[L:  1, P:  1]      |file:
[L:  1, P:  1]      |    statement:
[L:  1, P:  1]      |        delete_statement:
[L:  1, P:  1]      |            keyword:                                          'DELETE'
[L:  1, P:  7]      |            whitespace:                                       ' '
[L:  1, P:  8]      |            from_clause:
[L:  1, P:  8]      |                keyword:                                      'FROM'
[L:  1, P: 12]      |                whitespace:                                   ' '
[L:  1, P: 13]      |                from_expression:
[L:  1, P: 13]      |                    [META] indent:
[L:  1, P: 13]      |                    from_expression_element:
[L:  1, P: 13]      |                        table_expression:
[L:  1, P: 13]      |                            table_reference:
[L:  1, P: 13]      |                                identifier:                   'some_table'
[L:  1, P: 23]      |                    newline:                                  '\n'
[L:  2, P:  1]      |                    [META] dedent:
[L:  2, P:  1]      |                unparsable:                                   !! Expected: 'Nothing...'
[L:  2, P:  1]      |                    raw:                                      'USING'
[L:  2, P:  6]      |                    whitespace:                               ' '
[L:  2, P:  7]      |                    raw:                                      'other_table'
[L:  2, P: 18]      |            newline:                                          '\n'
[L:  3, P:  1]      |            where_clause:
[L:  3, P:  1]      |                keyword:                                      'WHERE'
[L:  3, P:  6]      |                [META] indent:
[L:  3, P:  6]      |                whitespace:                                   ' '
[L:  3, P:  7]      |                expression:
[L:  3, P:  7]      |                    column_reference:
[L:  3, P:  7]      |                        identifier:                           'other_table'
[L:  3, P: 18]      |                        dot:                                  '.'
[L:  3, P: 19]      |                        identifier:                           'var'
[L:  3, P: 22]      |                    whitespace:                               ' '
[L:  3, P: 23]      |                    comparison_operator:
[L:  3, P: 23]      |                        raw_comparison_operator:              '='
[L:  3, P: 24]      |                    whitespace:                               ' '
[L:  3, P: 25]      |                    column_reference:
[L:  3, P: 25]      |                        identifier:                           'some_table'
[L:  3, P: 35]      |                        dot:                                  '.'
[L:  3, P: 36]      |                        identifier:                           'var'
[L:  3, P: 39]      |                [META] dedent:
[L:  3, P: 39]      |    newline:                                                  '\n'

==== parsing violations ====
L:   2 | P:   1 |  PRS | Line 2, Position 1: Found unparsable section: 'USING other_table'
```


- `sqlfluff lint` output:

```
== [~/repositories/sqlfluff/sqlfluff_test.sql] FAIL
L:   2 | P:   1 |  PRS | Line 2, Position 1: Found unparsable section: 'USING
                       | other_table'
All Finished!
```


- `sqlfluff fix` output:

```
==== finding fixable violations ====
  [1 templating/parsing errors found]
==== no fixable linting violations found ====
All Finished!

```


## Dialect

postgres (& Redshift)

## Version

- Python 3.8.10
- sqlfluff, version 0.10.0

## Configuration

Default

Hints from issue discussion:

Commit message: Test cases

Patch to review:
diff --git a/src/sqlfluff/dialects/dialect_postgres.py b/src/sqlfluff/dialects/dialect_postgres.py
index d62d4121173..2c359d364ff 100644
--- a/src/sqlfluff/dialects/dialect_postgres.py
+++ b/src/sqlfluff/dialects/dialect_postgres.py
@@ -17,6 +17,7 @@
     SymbolSegment,
     StartsWith,
     CommentSegment,
+    Indent,
     Dedent,
     SegmentGenerator,
     NewlineSegment,
@@ -376,6 +377,16 @@
         Sequence(Ref("SimpleArrayTypeGrammar"), Ref("ArrayLiteralSegment")),
     ),
     SimpleArrayTypeGrammar=Ref.keyword("ARRAY"),
+    WhereClauseTerminatorGrammar=OneOf(
+        "LIMIT",
+        Sequence("GROUP", "BY"),
+        Sequence("ORDER", "BY"),
+        "HAVING",
+        "QUALIFY",
+        "WINDOW",
+        "OVERLAPS",
+        "RETURNING",
+    ),
 )
 
 
@@ -3638,3 +3649,81 @@ class ValuesClauseSegment(BaseSegment):
         # TO DO - CHECK OFFSET
         # TO DO - FETCH
     )
+
+
+@postgres_dialect.segment()
+class DeleteUsingClauseSegment(BaseSegment):
+    """USING clause."""
+
+    type = "using_clause"
+    match_grammar = StartsWith(
+        "USING",
+        terminator="WHERE",
+        enforce_whitespace_preceding_terminator=True,
+    )
+
+    parse_grammar = Sequence(
+        "USING",
+        Indent,
+        # NB: We don't use BracketedColumnReferenceListGrammar
+        # here because we're just using SingleIdentifierGrammar,
+        # rather than ObjectReferenceSegment or
+        # ColumnReferenceSegment.
+        # This is a) so that we don't lint it as a reference and
+        # b) because the column will probably be returned anyway
+        # during parsing.
+        Delimited(
+            Ref("TableExpressionSegment"),
+            ephemeral_name="UsingClauseContents",
+        ),
+        Dedent,
+    )
+
+
+@postgres_dialect.segment()
+class FromClauseTerminatingUsingWhereSegment(
+    ansi_dialect.get_segment("FromClauseSegment")  # type: ignore
+):
+    """Copy `FROM` terminator statement to support `USING` in specific circumstances."""
+
+    match_grammar = StartsWith(
+        "FROM",
+        terminator=OneOf(Ref.keyword("USING"), Ref.keyword("WHERE")),
+        enforce_whitespace_preceding_terminator=True,
+    )
+
+
+@postgres_dialect.segment(replace=True)
+class DeleteStatementSegment(BaseSegment):
+    """A `DELETE` statement.
+
+    https://www.postgresql.org/docs/14/sql-delete.html
+    """
+
+    type = "delete_statement"
+    # TODO Implement WITH RECURSIVE
+    match_grammar = StartsWith("DELETE")
+    parse_grammar = Sequence(
+        "DELETE",
+        Ref.keyword("ONLY", optional=True),
+        Ref("FromClauseTerminatingUsingWhereSegment"),
+        # TODO Implement Star and As Alias
+        Ref("DeleteUsingClauseSegment", optional=True),
+        OneOf(
+            Sequence("WHERE", "CURRENT", "OF", Ref("ObjectReferenceSegment")),
+            Ref("WhereClauseSegment"),
+            optional=True,
+        ),
+        Sequence(
+            "RETURNING",
+            OneOf(
+                Ref("StarSegment"),
+                Sequence(
+                    Ref("ExpressionSegment"),
+                    Ref("AliasSegment", optional=True),
+                ),
+                optional=True,
+            ),
+            optional=True,
+        ),
+    )
diff --git a/test/fixtures/dialects/postgres/postgres_delete.sql b/test/fixtures/dialects/postgres/postgres_delete.sql
new file mode 100644
index 00000000000..a9b2004bd63
--- /dev/null
+++ b/test/fixtures/dialects/postgres/postgres_delete.sql
@@ -0,0 +1,12 @@
+DELETE FROM films WHERE kind <> 'Musical';
+
+DELETE FROM films;
+
+DELETE FROM tasks WHERE status = 'DONE' RETURNING *;
+
+DELETE FROM tasks WHERE CURRENT OF c_tasks;
+
+DELETE FROM some_table
+USING other_table
+WHERE other_table.col = some_table.col
+
diff --git a/test/fixtures/dialects/postgres/postgres_delete.yml b/test/fixtures/dialects/postgres/postgres_delete.yml
new file mode 100644
index 00000000000..c5ed948b3db
--- /dev/null
+++ b/test/fixtures/dialects/postgres/postgres_delete.yml
@@ -0,0 +1,103 @@
+# 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: 7fb777ecc5b4fc3836714dd400198a54b212075c86a388190bf8d05c6c4ac6f4
+file:
+- statement:
+    delete_statement:
+      keyword: DELETE
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                identifier: films
+      where_clause:
+        keyword: WHERE
+        expression:
+          column_reference:
+            identifier: kind
+          comparison_operator:
+          - raw_comparison_operator: <
+          - raw_comparison_operator: '>'
+          literal: "'Musical'"
+- statement_terminator: ;
+- statement:
+    delete_statement:
+      keyword: DELETE
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                identifier: films
+- statement_terminator: ;
+- statement:
+    delete_statement:
+    - keyword: DELETE
+    - from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                identifier: tasks
+    - where_clause:
+        keyword: WHERE
+        expression:
+          column_reference:
+            identifier: status
+          comparison_operator:
+            raw_comparison_operator: '='
+          literal: "'DONE'"
+    - keyword: RETURNING
+    - star: '*'
+- statement_terminator: ;
+- statement:
+    delete_statement:
+    - keyword: DELETE
+    - from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                identifier: tasks
+    - keyword: WHERE
+    - keyword: CURRENT
+    - keyword: OF
+    - object_reference:
+        identifier: c_tasks
+- statement_terminator: ;
+- statement:
+    delete_statement:
+      keyword: DELETE
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                identifier: some_table
+      using_clause:
+        keyword: USING
+        table_expression:
+          table_reference:
+            identifier: other_table
+      where_clause:
+        keyword: WHERE
+        expression:
+        - column_reference:
+          - identifier: other_table
+          - dot: .
+          - identifier: col
+        - comparison_operator:
+            raw_comparison_operator: '='
+        - column_reference:
+          - identifier: some_table
+          - dot: .
+          - identifier: col


Code review comments:
diff_hunk: @@ -3638,3 +3649,81 @@ class ValuesClauseSegment(BaseSegment):
         # TO DO - CHECK OFFSET
         # TO DO - FETCH
     )
+
+
+@postgres_dialect.segment()
+class DeleteUsingClauseSegment(BaseSegment):
+    """USING clause."""
+
+    type = "using_clause"
+    match_grammar = StartsWith(
+        "USING",
+        terminator="WHERE",
+        enforce_whitespace_preceding_terminator=True,
+    )
+
+    parse_grammar = Sequence(
+        "USING",
+        Indent,
+        # NB: We don't use BracketedColumnReferenceListGrammar
path: src/sqlfluff/dialects/dialect_postgres.py
text: @user1:
I'm having trouble relating this comment to the code. The code uses `TableExpressionSegment`, but the comment says we're using `SingleIdentifierGrammar`. 🤯 

@author:
Remove this whole comment. Copied over from code I stole this from.