Repository: sqlfluff/sqlfluff

Issue:
sqlfluff-fix pre-commit hook breaks INSERT statements due to column reordering
This issue came up when using the pre-commit hook `sqlfluff-fix` as mentioned in the documentation https://docs.sqlfluff.com/en/stable/production.html#using-pre-commit

## Expected Behaviour
After running `sqlfluff-fix`, the output should be
```
INSERT INTO example_schema.example_table
(id,
    example_column,
    rank_asc,
    rank_desc)
SELECT
    id,
    CASE
        WHEN col_a IN('a',
                              'b',
                'c') THEN col_a
    END AS example_column,
    rank_asc,
    rank_desc
FROM
  another_schema.another_table
```

## Observed Behaviour
Running the pre-commit hook `sqlfluff-fix` reorders columns in the SELECT clause without making the same modification in the INSERT clause, thus causing an error.
```
INSERT INTO example_schema.example_table
(id,
    example_column,
    rank_asc,
    rank_desc)
SELECT
    id,
    rank_asc,
    rank_desc,
    CASE
        WHEN col_a IN('a',
                              'b',
                'c') THEN col_a
    END AS example_column
FROM
  another_schema.another_table
```
## Steps to Reproduce
- add the example SQL to a file
- `git add` the file
- `git commit -m "testing my change"`
- check the fixed file after `sqlfluff-fix` pre-commit hook has finished fixing the SQL

## Version
```
sqlfluff, version 0.6.2
Python 3.7.6
```
## Configuration
```
[sqlfluff]
verbose=1
dialect=snowflake
ignore=parsing,lexing
max_line_length=200

[sqlfluff:rules:L010]  # Keywords
capitalisation_policy=upper

[sqlfluff:rules:L014]  # Unquoted identifiers
extended_capitalisation_policy=lower

[sqlfluff:rules:L030]  # Function names
capitalisation_policy=lower

[sqlfluff:rules:L040]  # Null & Boolean Literals
capitalisation_policy=upper
```

Discussion:

Patch:
diff --git a/src/sqlfluff/rules/L034.py b/src/sqlfluff/rules/L034.py
index e66d3acde..b47089615 100644
--- a/src/sqlfluff/rules/L034.py
+++ b/src/sqlfluff/rules/L034.py
@@ -64,10 +64,23 @@ class Rule_L034(BaseRule):
         # If we find a matching target element, we append the element to the corresponding index
         self.seen_band_elements = [[] for i in select_element_order_preference] + [[]]
 
-        # Ignore select clauses which belong to a set expression, which are most commonly a union.
-        if segment.is_type("select_clause") and not parent_stack[-2].is_type(
-            "set_expression"
-        ):
+        if segment.is_type("select_clause"):
+            # Ignore select clauses which belong to:
+            # - set expression, which is most commonly a union
+            # - insert_statement
+            # - create table statement
+            #
+            # In each of these contexts, the order of columns in a select should
+            # be preserved.
+            if len(parent_stack) >= 2 and parent_stack[-2].is_type(
+                "insert_statement", "set_expression"
+            ):
+                return None
+            if len(parent_stack) >= 3 and parent_stack[-3].is_type(
+                "create_table_statement"
+            ):
+                return None
+
             select_clause_segment = segment
             select_target_elements = segment.get_children("select_clause_element")
             if not select_target_elements:


Test patch:
diff --git a/test/fixtures/rules/std_rule_cases/L034.yml b/test/fixtures/rules/std_rule_cases/L034.yml
index 3f3c30f62..b88156f5a 100644
--- a/test/fixtures/rules/std_rule_cases/L034.yml
+++ b/test/fixtures/rules/std_rule_cases/L034.yml
@@ -101,3 +101,38 @@ test_union_statements_ignored:
       c,
       d
     from table_b
+
+test_insert_statements_ignored:
+  pass_str: |
+    INSERT INTO example_schema.example_table
+    (id,
+        example_column,
+        rank_asc,
+        rank_desc)
+    SELECT
+        id,
+        CASE
+            WHEN col_a IN('a',
+                                  'b',
+                    'c') THEN col_a
+        END AS example_column,
+        rank_asc,
+        rank_desc
+    FROM
+      another_schema.another_table
+
+test_create_table_as_select_statements_ignored:
+  pass_str: |
+    CREATE TABLE new_table AS (
+      SELECT
+          id,
+          CASE
+              WHEN col_a IN('a',
+                                    'b',
+                      'c') THEN col_a
+          END AS example_column,
+          rank_asc,
+          rank_desc
+      FROM
+        another_schema.another_table
+    )