Repository: sqlfluff/sqlfluff

Issue:
`sqlfluff fix` mixes hanging and regular indents
## Steps to Reproduce

Run `sqlfluff fix` on this code:
``` sql
select *
from foo
where a like 'a%'
  and b like 'b%'
  and c = false
```

## Expected Behaviour

Either one of these options:
``` sql
select *
from foo
where a like 'a%'
      and b like 'b%'
      and c = false
```
or
``` sql
select *
from foo
where a like 'a%'
    and b like 'b%'
    and c = false
```

## Observed Behaviour

`sqlfluff fix` produces this output, which looks worse than any of the alternatives:

``` sql
select *
from foo
where a like 'a%'
      and b like 'b%'
    and c = false
```

## Version
SQLFluff 0.6.3
Python 3.9.4

Discussion:

Patch:
diff --git a/src/sqlfluff/rules/L003.py b/src/sqlfluff/rules/L003.py
index b657d44f1..5678d1560 100644
--- a/src/sqlfluff/rules/L003.py
+++ b/src/sqlfluff/rules/L003.py
@@ -94,7 +94,7 @@ class Rule_L003(BaseRule):
                     # Indent balance is the indent at the start of the first content
                     "indent_balance": this_indent_balance,
                     "hanging_indent": hanger_pos if line_indent_stack else None,
-                    # Clean indent is true if the line *ends* win an indent
+                    # Clean indent is true if the line *ends* with an indent
                     # or has an indent in the initial whitespace.
                     "clean_indent": clean_indent,
                 }
@@ -436,8 +436,7 @@ class Rule_L003(BaseRule):
                     )
                     # If we have a clean indent, we can just add steps in line
                     # with the difference in the indent buffers. simples.
-                    # We can also do this if we've skipped a line. I think?
-                    if this_line["clean_indent"] or this_line_no - k > 1:
+                    if this_line["clean_indent"]:
                         self.logger.debug("        Use clean indent.")
                         desired_indent = default_indent
                     # If we have the option of a hanging indent then use it.
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/L003.yml b/test/fixtures/rules/std_rule_cases/L003.yml
index f87b2ba4c..c6f68db0d 100644
--- a/test/fixtures/rules/std_rule_cases/L003.yml
+++ b/test/fixtures/rules/std_rule_cases/L003.yml
@@ -256,6 +256,28 @@ test_fail_possible_hanger_fix:
                     bar)
     FROM tbl
 
+test_fail_consecutive_hangers:
+  fail_str: |
+    select *
+    from foo
+    where a like 'a%'
+      and b like 'b%'
+      and c like 'c%'
+      and d like 'd%'
+      and e like 'e%'
+      and f like 'f%'
+
+  fix_str: |
+    select *
+    from foo
+    where a like 'a%'
+          and b like 'b%'
+          and c like 'c%'
+          and d like 'd%'
+          and e like 'e%'
+          and f like 'f%'
+
+
 test_fail_clean_reindent_fix:
   # A "clean" indent is where the previous line ends with an
   # indent token (as per this example). We should use the
@@ -298,4 +320,4 @@ test_pass_indent_indent_bigquery:
   
   configs:
     core:
-      dialect: bigquery
\ No newline at end of file
+      dialect: bigquery
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
+    )