Repository: sqlfluff/sqlfluff
Issue category: Feature Request

SQLite Dialect RETURNING Support
### Search before asking

- [X] I searched the [issues](https://github.com/sqlfluff/sqlfluff/issues) and found no similar issues.


### What Happened

SQLFluff produces `Found unparsable section` for any SQLite statements containing a `RETURNING` clause.

### Expected Behaviour

SQLite supports [RETURNING](https://www.sqlite.org/lang_returning.html) clauses after INSERT, UPDATE, and DELETE statements since [version 3.35](https://www.sqlite.org/releaselog/3_35_0.html). Sqlfluff should parse those clauses as valid sql.

### Observed Behaviour

SQLFluff fails to parse any statement containing a `RETURNING` clause when using the sqlite dialect.

### How to reproduce

command
```bash
$ sqlfluff parse query.sql
```
query.sql
```sql
INSERT INTO users (name, email) VALUES (?, ?) RETURNING *;
```

### Dialect

sqlite

### Version

sqlfluff, version 2.3.5
Python 3.11.5

### Configuration

```toml
[sqlfluff]
dialect = sqlite
templater = placeholder

[sqlfluff:templater:placeholder]
param_style = question_mark
```

### Are you willing to work on and submit a PR to address the issue?

- [X] Yes I am willing to submit a PR!

### Code of Conduct

- [X] I agree to follow this project's [Code of Conduct](https://github.com/sqlfluff/sqlfluff/blob/main/CODE_OF_CONDUCT.md)



Functions to edit:
src/sqlfluff/dialects/dialect_sqlite.py:InsertStatementSegment
Functions added: src/sqlfluff/dialects/dialect_sqlite.py:ReturningClauseSegment
src/sqlfluff/dialects/dialect_sqlite.py:DeleteStatementSegment
src/sqlfluff/dialects/dialect_sqlite.py:UpdateStatementSegment

Patch:
diff --git a/src/sqlfluff/dialects/dialect_sqlite.py b/src/sqlfluff/dialects/dialect_sqlite.py
index a0180af8db5..85c9c23014c 100644
--- a/src/sqlfluff/dialects/dialect_sqlite.py
+++ b/src/sqlfluff/dialects/dialect_sqlite.py
@@ -288,6 +288,26 @@ class IndexColumnDefinitionSegment(BaseSegment):
     )
 
 
+class ReturningClauseSegment(BaseSegment):
+    """A returning clause.
+
+    Per docs https://www.sqlite.org/lang_returning.html
+    """
+
+    type = "returning_clause"
+
+    match_grammar = Sequence(
+        "RETURNING",
+        Delimited(
+            Ref("WildcardExpressionSegment"),
+            Sequence(
+                Ref("ExpressionSegment"),
+                Ref("AliasExpressionSegment", optional=True),
+            ),
+        ),
+    )
+
+
 class InsertStatementSegment(BaseSegment):
     """An`INSERT` statement.
 
@@ -322,6 +342,7 @@ class InsertStatementSegment(BaseSegment):
             OptionallyBracketed(Ref("SelectableGrammar")),
             Ref("DefaultValuesGrammar"),
         ),
+        Ref("ReturningClauseSegment", optional=True),
     )
 
 
@@ -510,6 +531,43 @@ class UnorderedSelectStatementSegment(BaseSegment):
     )
 
 
+class DeleteStatementSegment(ansi.DeleteStatementSegment):
+    """A `DELETE` statement.
+
+    DELETE FROM <table name> [ WHERE <search condition> ]
+    """
+
+    type = "delete_statement"
+    # match grammar. This one makes sense in the context of knowing that it's
+    # definitely a statement, we just don't know what type yet.
+    match_grammar: Matchable = Sequence(
+        "DELETE",
+        Ref("FromClauseSegment"),
+        Ref("WhereClauseSegment", optional=True),
+        Ref("ReturningClauseSegment", optional=True),
+    )
+
+
+class UpdateStatementSegment(ansi.UpdateStatementSegment):
+    """An `Update` statement.
+
+    UPDATE <table name> SET <set clause list> [ WHERE <search condition> ]
+    """
+
+    type = "update_statement"
+    match_grammar: Matchable = Sequence(
+        "UPDATE",
+        Ref("TableReferenceSegment"),
+        # SET is not a reserved word in all dialects (e.g. RedShift)
+        # So specifically exclude as an allowed implicit alias to avoid parsing errors
+        Ref("AliasExpressionSegment", exclude=Ref.keyword("SET"), optional=True),
+        Ref("SetClauseListSegment"),
+        Ref("FromClauseSegment", optional=True),
+        Ref("WhereClauseSegment", optional=True),
+        Ref("ReturningClauseSegment", optional=True),
+    )
+
+
 class SelectStatementSegment(BaseSegment):
     """A `SELECT` statement.
 

Test patch: diff --git a/test/fixtures/dialects/sqlite/delete.sql b/test/fixtures/dialects/sqlite/delete.sql
new file mode 100644
index 00000000000..0b278b4e519
--- /dev/null
+++ b/test/fixtures/dialects/sqlite/delete.sql
@@ -0,0 +1,17 @@
+DELETE FROM table_name
+WHERE a > 0;
+
+DELETE FROM table_name
+WHERE a > 0
+RETURNING *
+;
+
+DELETE FROM table_name
+WHERE a > 0
+RETURNING *, id
+;
+
+DELETE FROM table_name
+WHERE a > 0
+RETURNING id foo, id_2 AS bar
+;
diff --git a/test/fixtures/dialects/sqlite/delete.yml b/test/fixtures/dialects/sqlite/delete.yml
new file mode 100644
index 00000000000..b055f3166da
--- /dev/null
+++ b/test/fixtures/dialects/sqlite/delete.yml
@@ -0,0 +1,111 @@
+# 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: 3f2c19b483d3d69bcfa443adca57d29f3340b18f788942ba2e535ed9d182e870
+file:
+- statement:
+    delete_statement:
+      keyword: DELETE
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                naked_identifier: table_name
+      where_clause:
+        keyword: WHERE
+        expression:
+          column_reference:
+            naked_identifier: a
+          comparison_operator:
+            raw_comparison_operator: '>'
+          numeric_literal: '0'
+- statement_terminator: ;
+- statement:
+    delete_statement:
+      keyword: DELETE
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                naked_identifier: table_name
+      where_clause:
+        keyword: WHERE
+        expression:
+          column_reference:
+            naked_identifier: a
+          comparison_operator:
+            raw_comparison_operator: '>'
+          numeric_literal: '0'
+      returning_clause:
+        keyword: RETURNING
+        wildcard_expression:
+          wildcard_identifier:
+            star: '*'
+- statement_terminator: ;
+- statement:
+    delete_statement:
+      keyword: DELETE
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                naked_identifier: table_name
+      where_clause:
+        keyword: WHERE
+        expression:
+          column_reference:
+            naked_identifier: a
+          comparison_operator:
+            raw_comparison_operator: '>'
+          numeric_literal: '0'
+      returning_clause:
+        keyword: RETURNING
+        wildcard_expression:
+          wildcard_identifier:
+            star: '*'
+        comma: ','
+        expression:
+          column_reference:
+            naked_identifier: id
+- statement_terminator: ;
+- statement:
+    delete_statement:
+      keyword: DELETE
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                naked_identifier: table_name
+      where_clause:
+        keyword: WHERE
+        expression:
+          column_reference:
+            naked_identifier: a
+          comparison_operator:
+            raw_comparison_operator: '>'
+          numeric_literal: '0'
+      returning_clause:
+      - keyword: RETURNING
+      - expression:
+          column_reference:
+            naked_identifier: id
+      - alias_expression:
+          naked_identifier: foo
+      - comma: ','
+      - expression:
+          column_reference:
+            naked_identifier: id_2
+      - alias_expression:
+          keyword: AS
+          naked_identifier: bar
+- statement_terminator: ;
diff --git a/test/fixtures/dialects/sqlite/insert.sql b/test/fixtures/dialects/sqlite/insert.sql
index 2e1fdea866b..621e3c3feef 100644
--- a/test/fixtures/dialects/sqlite/insert.sql
+++ b/test/fixtures/dialects/sqlite/insert.sql
@@ -23,3 +23,13 @@ SELECT * FROM (SELECT
 INSERT INTO t1 DEFAULT VALUES;
 
 INSERT INTO t1 (a, b, c) DEFAULT VALUES;
+
+INSERT INTO t1 (a, b, c) DEFAULT VALUES RETURNING *;
+
+INSERT INTO t1 (a, b, c) DEFAULT VALUES RETURNING a foo;
+
+INSERT INTO t1 (a, b, c) DEFAULT VALUES RETURNING a AS foo, *;
+
+INSERT INTO t1 (a, b, c) DEFAULT VALUES RETURNING a AS foo;
+
+INSERT INTO users (name, email) VALUES (1, 2) RETURNING *;
diff --git a/test/fixtures/dialects/sqlite/insert.yml b/test/fixtures/dialects/sqlite/insert.yml
index 2e676e9395d..53e6ab76fa0 100644
--- a/test/fixtures/dialects/sqlite/insert.yml
+++ b/test/fixtures/dialects/sqlite/insert.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: 5f028ef635cbb9c766ad3b82ae3dffb38d47f1bb204455cd04172af9cfd4d0cc
+_hash: 4c9fda9662efe66848333fec74b43dbc72ccc1bca146a4b743cc4c4f9bceebe8
 file:
 - statement:
     insert_statement:
@@ -426,3 +426,145 @@ file:
     - keyword: DEFAULT
     - keyword: VALUES
 - statement_terminator: ;
+- statement:
+    insert_statement:
+    - keyword: INSERT
+    - keyword: INTO
+    - table_reference:
+        naked_identifier: t1
+    - bracketed:
+      - start_bracket: (
+      - column_reference:
+          naked_identifier: a
+      - comma: ','
+      - column_reference:
+          naked_identifier: b
+      - comma: ','
+      - column_reference:
+          naked_identifier: c
+      - end_bracket: )
+    - keyword: DEFAULT
+    - keyword: VALUES
+    - returning_clause:
+        keyword: RETURNING
+        wildcard_expression:
+          wildcard_identifier:
+            star: '*'
+- statement_terminator: ;
+- statement:
+    insert_statement:
+    - keyword: INSERT
+    - keyword: INTO
+    - table_reference:
+        naked_identifier: t1
+    - bracketed:
+      - start_bracket: (
+      - column_reference:
+          naked_identifier: a
+      - comma: ','
+      - column_reference:
+          naked_identifier: b
+      - comma: ','
+      - column_reference:
+          naked_identifier: c
+      - end_bracket: )
+    - keyword: DEFAULT
+    - keyword: VALUES
+    - returning_clause:
+        keyword: RETURNING
+        expression:
+          column_reference:
+            naked_identifier: a
+        alias_expression:
+          naked_identifier: foo
+- statement_terminator: ;
+- statement:
+    insert_statement:
+    - keyword: INSERT
+    - keyword: INTO
+    - table_reference:
+        naked_identifier: t1
+    - bracketed:
+      - start_bracket: (
+      - column_reference:
+          naked_identifier: a
+      - comma: ','
+      - column_reference:
+          naked_identifier: b
+      - comma: ','
+      - column_reference:
+          naked_identifier: c
+      - end_bracket: )
+    - keyword: DEFAULT
+    - keyword: VALUES
+    - returning_clause:
+        keyword: RETURNING
+        expression:
+          column_reference:
+            naked_identifier: a
+        alias_expression:
+          keyword: AS
+          naked_identifier: foo
+        comma: ','
+        wildcard_expression:
+          wildcard_identifier:
+            star: '*'
+- statement_terminator: ;
+- statement:
+    insert_statement:
+    - keyword: INSERT
+    - keyword: INTO
+    - table_reference:
+        naked_identifier: t1
+    - bracketed:
+      - start_bracket: (
+      - column_reference:
+          naked_identifier: a
+      - comma: ','
+      - column_reference:
+          naked_identifier: b
+      - comma: ','
+      - column_reference:
+          naked_identifier: c
+      - end_bracket: )
+    - keyword: DEFAULT
+    - keyword: VALUES
+    - returning_clause:
+        keyword: RETURNING
+        expression:
+          column_reference:
+            naked_identifier: a
+        alias_expression:
+          keyword: AS
+          naked_identifier: foo
+- statement_terminator: ;
+- statement:
+    insert_statement:
+    - keyword: INSERT
+    - keyword: INTO
+    - table_reference:
+        naked_identifier: users
+    - bracketed:
+      - start_bracket: (
+      - column_reference:
+          naked_identifier: name
+      - comma: ','
+      - column_reference:
+          naked_identifier: email
+      - end_bracket: )
+    - values_clause:
+        keyword: VALUES
+        bracketed:
+        - start_bracket: (
+        - expression:
+            numeric_literal: '1'
+        - comma: ','
+        - expression:
+            numeric_literal: '2'
+        - end_bracket: )
+    - returning_clause:
+        keyword: RETURNING
+        wildcard_expression:
+          wildcard_identifier:
+            star: '*'
+- statement_terminator: ;
diff --git a/test/fixtures/dialects/sqlite/update.sql b/test/fixtures/dialects/sqlite/update.sql
new file mode 100644
index 00000000000..253535c6f69
--- /dev/null
+++ b/test/fixtures/dialects/sqlite/update.sql
@@ -0,0 +1,5 @@
+UPDATE table_name SET column1 = value1, column2 = value2 WHERE a=1;
+
+UPDATE table_name SET column1 = value1, column2 = value2 WHERE a=1 RETURNING *;
+
+UPDATE table_name SET column1 = value1, column2 = value2 WHERE a=1 RETURNING id foo, id_2 AS bar;
diff --git a/test/fixtures/dialects/sqlite/update.yml b/test/fixtures/dialects/sqlite/update.yml
new file mode 100644
index 00000000000..6fef8e73da3
--- /dev/null
+++ b/test/fixtures/dialects/sqlite/update.yml
@@ -0,0 +1,119 @@
+# 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: ae5a79c638d3c45f8d9c1f249cb7f74c6bb5e70786f1b26407e8894e6e77d7ba
+file:
+- statement:
+    update_statement:
+      keyword: UPDATE
+      table_reference:
+        naked_identifier: table_name
+      set_clause_list:
+      - keyword: SET
+      - set_clause:
+        - column_reference:
+            naked_identifier: column1
+        - comparison_operator:
+            raw_comparison_operator: '='
+        - column_reference:
+            naked_identifier: value1
+      - comma: ','
+      - set_clause:
+        - column_reference:
+            naked_identifier: column2
+        - comparison_operator:
+            raw_comparison_operator: '='
+        - column_reference:
+            naked_identifier: value2
+      where_clause:
+        keyword: WHERE
+        expression:
+          column_reference:
+            naked_identifier: a
+          comparison_operator:
+            raw_comparison_operator: '='
+          numeric_literal: '1'
+- statement_terminator: ;
+- statement:
+    update_statement:
+      keyword: UPDATE
+      table_reference:
+        naked_identifier: table_name
+      set_clause_list:
+      - keyword: SET
+      - set_clause:
+        - column_reference:
+            naked_identifier: column1
+        - comparison_operator:
+            raw_comparison_operator: '='
+        - column_reference:
+            naked_identifier: value1
+      - comma: ','
+      - set_clause:
+        - column_reference:
+            naked_identifier: column2
+        - comparison_operator:
+            raw_comparison_operator: '='
+        - column_reference:
+            naked_identifier: value2
+      where_clause:
+        keyword: WHERE
+        expression:
+          column_reference:
+            naked_identifier: a
+          comparison_operator:
+            raw_comparison_operator: '='
+          numeric_literal: '1'
+      returning_clause:
+        keyword: RETURNING
+        wildcard_expression:
+          wildcard_identifier:
+            star: '*'
+- statement_terminator: ;
+- statement:
+    update_statement:
+      keyword: UPDATE
+      table_reference:
+        naked_identifier: table_name
+      set_clause_list:
+      - keyword: SET
+      - set_clause:
+        - column_reference:
+            naked_identifier: column1
+        - comparison_operator:
+            raw_comparison_operator: '='
+        - column_reference:
+            naked_identifier: value1
+      - comma: ','
+      - set_clause:
+        - column_reference:
+            naked_identifier: column2
+        - comparison_operator:
+            raw_comparison_operator: '='
+        - column_reference:
+            naked_identifier: value2
+      where_clause:
+        keyword: WHERE
+        expression:
+          column_reference:
+            naked_identifier: a
+          comparison_operator:
+            raw_comparison_operator: '='
+          numeric_literal: '1'
+      returning_clause:
+      - keyword: RETURNING
+      - expression:
+          column_reference:
+            naked_identifier: id
+      - alias_expression:
+          naked_identifier: foo
+      - comma: ','
+      - expression:
+          column_reference:
+            naked_identifier: id_2
+      - alias_expression:
+          keyword: AS
+          naked_identifier: bar
+- statement_terminator: ;