Repository: sqlfluff/sqlfluff
Issue category: Feature Request

Support CTEs in CREATE VIEW statements in MySQL
### Search before asking

- [X] I searched the [issues](https://github.com/sqlfluff/sqlfluff/issues) and found no similar issues.


### Description

The statement 
```
CREATE OR REPLACE VIEW vw_test AS (
  WITH testing as (
      SELECT
        a
        , b
      FROM t1
  )
  SELECT a from sids
);
```

will fail a parse: 
```
L:   1 | P:   1 |  PRS | Line 1, Position 1: Found unparsable section: 'CREATE OR
                       | REPLACE VIEW vw_test AS (\n  WI...'
```

if run with the mysql dialect, but will run successfully on mysql and will parse successfully with the ansi dialect

### Use case

Should pass linting

### Dialect

mysql

### 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_mysql.py:AlterViewStatementSegment
src/sqlfluff/dialects/dialect_mysql.py:CreateViewStatementSegment
src/sqlfluff/dialects/dialect_mysql.py:UnorderedSelectStatementSegment

Patch:
diff --git a/src/sqlfluff/dialects/dialect_mysql.py b/src/sqlfluff/dialects/dialect_mysql.py
index 06b9424cce6..769276d9b5e 100644
--- a/src/sqlfluff/dialects/dialect_mysql.py
+++ b/src/sqlfluff/dialects/dialect_mysql.py
@@ -176,6 +176,7 @@
             Ref("ForClauseSegment"),
             Ref("SetOperatorSegment"),
             Ref("WithNoSchemaBindingClauseSegment"),
+            Ref("WithCheckOptionSegment"),
             Ref("IntoClauseSegment"),
         ]
     ),
@@ -1649,12 +1650,7 @@ class AlterViewStatementSegment(BaseSegment):
         Ref("TableReferenceSegment"),
         Ref("BracketedColumnReferenceListGrammar", optional=True),
         "AS",
-        OptionallyBracketed(
-            OneOf(
-                Ref("SelectStatementSegment"),
-                Ref("SetExpressionSegment"),
-            )
-        ),
+        OptionallyBracketed(Ref("SelectableGrammar")),
         Ref("WithCheckOptionSegment", optional=True),
     )
 
@@ -1682,12 +1678,7 @@ class CreateViewStatementSegment(BaseSegment):
         Ref("TableReferenceSegment"),
         Ref("BracketedColumnReferenceListGrammar", optional=True),
         "AS",
-        OptionallyBracketed(
-            OneOf(
-                Ref("SelectStatementSegment"),
-                Ref("SetExpressionSegment"),
-            )
-        ),
+        OptionallyBracketed(Ref("SelectableGrammar")),
         Ref("WithCheckOptionSegment", optional=True),
     )
 
@@ -1928,6 +1919,7 @@ class UnorderedSelectStatementSegment(ansi.UnorderedSelectStatementSegment):
                 Ref("IntoClauseSegment"),
                 Ref("ForClauseSegment"),
                 Ref("IndexHintClauseSegment"),
+                Ref("WithCheckOptionSegment"),
                 Ref("SelectPartitionClauseSegment"),
                 Ref("UpsertClauseListSegment"),
             ],

Test patch: diff --git a/test/fixtures/dialects/mysql/create_view.sql b/test/fixtures/dialects/mysql/create_view.sql
index 39e80156052..c82144e774d 100644
--- a/test/fixtures/dialects/mysql/create_view.sql
+++ b/test/fixtures/dialects/mysql/create_view.sql
@@ -1,12 +1,24 @@
 CREATE VIEW v1 (c,d) AS
-SELECT a,b FROM t1;
+  SELECT a,b FROM t1;
 
 CREATE OR REPLACE VIEW v1 (c,d,e,f) AS
-SELECT a,b, a IN (SELECT a+2 FROM t1), a = all (SELECT a FROM t1) FROM t1;
+  SELECT a,b, a IN (SELECT a+2 FROM t1), a = all (SELECT a FROM t1) FROM t1;
 
 CREATE VIEW v2 AS SELECT a FROM t1 WITH CASCADED CHECK OPTION;
 
 CREATE VIEW v2 AS (SELECT a FROM t1) WITH CASCADED CHECK OPTION;
 
 CREATE VIEW v2 AS
-SELECT 1 UNION SELECT 2;
+  SELECT 1 UNION SELECT 2;
+
+
+
+CREATE VIEW vw_test AS
+  WITH testing_cte as (
+    SELECT
+      a
+      , b
+      FROM t1
+  )
+  SELECT a, b from testing_cte
+;
diff --git a/test/fixtures/dialects/mysql/create_view.yml b/test/fixtures/dialects/mysql/create_view.yml
index c0749de14f1..493a22b6cc9 100644
--- a/test/fixtures/dialects/mysql/create_view.yml
+++ b/test/fixtures/dialects/mysql/create_view.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: 3bb1e638499d52434c20d168ce195274b3a557ac2b38cf7244b16d97e841d3aa
+_hash: 3e9bbd59a0d1ce854d53cbe5270251f1551a34b005c745aa4d0df38434202969
 file:
 - statement:
     create_view_statement:
@@ -207,3 +207,53 @@ file:
             select_clause_element:
               numeric_literal: '2'
 - statement_terminator: ;
+- statement:
+    create_view_statement:
+    - keyword: CREATE
+    - keyword: VIEW
+    - table_reference:
+        naked_identifier: vw_test
+    - keyword: AS
+    - with_compound_statement:
+        keyword: WITH
+        common_table_expression:
+          naked_identifier: testing_cte
+          keyword: as
+          bracketed:
+            start_bracket: (
+            select_statement:
+              select_clause:
+              - keyword: SELECT
+              - select_clause_element:
+                  column_reference:
+                    naked_identifier: a
+              - comma: ','
+              - select_clause_element:
+                  column_reference:
+                    naked_identifier: b
+              from_clause:
+                keyword: FROM
+                from_expression:
+                  from_expression_element:
+                    table_expression:
+                      table_reference:
+                        naked_identifier: t1
+            end_bracket: )
+        select_statement:
+          select_clause:
+          - keyword: SELECT
+          - select_clause_element:
+              column_reference:
+                naked_identifier: a
+          - comma: ','
+          - select_clause_element:
+              column_reference:
+                naked_identifier: b
+          from_clause:
+            keyword: from
+            from_expression:
+              from_expression_element:
+                table_expression:
+                  table_reference:
+                    naked_identifier: testing_cte
+- statement_terminator: ;