Repository: sqlfluff/sqlfluff
Pull request: Issues 854, 1321: Handle Jinja leading whitespace-only lines
Fixes #854, #1321 
Preserves leading whitespace-only lines in SQL files when the Jinja templater is used.

### 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/parser` (note YML files can be auto generated with `python test/generate_parse_fixture_yml.py` or by running `tox` locally).
  - 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:
Fix command mangles file when jinja comment is present
## Expected Behaviour
Correctly fix trailing commas from input file.

Input file `stg_payments.sql`:
```sql
with source as (

    {#-
    Normally we would select from the table here, but we are using seeds to load
    our data in this project
    #}
    select * from {{ ref('raw_payments') }}

),

renamed as (

    select
        id as payment_id
        , order_id
        , payment_method
        --`amount` is currently stored in cents, so we convert it to dollars
        , amount / 100 as amount

    from source

)

select * from renamed
```

`sqlfluff fix` command line output:
```bash
==== finding fixable violations ====
== [models/staging/stg_payments.sql] FAIL
L:  10 | P:   9 | L019 | Found leading comma. Expected only trailing.
L:  11 | P:   9 | L019 | Found leading comma. Expected only trailing.
L:  13 | P:   9 | L019 | Found leading comma. Expected only trailing.
==== fixing violations ====
3 fixable linting violations found
Are you sure you wish to attempt to fix these? [Y/n] ...
Attempting fixes...
Persisting Changes...
== [models/staging/stg_payments.sql] PASS
Done. Please check your files to confirm.
```

Expected `stg_payments.sql` output:
```sql
with source as (

    {#-
    Normally we would select from the table here, but we are using seeds to load
    our data in this project
    #}
    select * from {{ ref('raw_payments') }}

),

renamed as (

    select
        id as payment_id,
        order_id,
        payment_method,
        --`amount` is currently stored in cents, so we convert it to dollars
        amount / 100 as amount

    from source

)

select * from renamed
```

## Observed Behaviour
Actual output:

```sql
with source as (

    {#-
    Normally we would select from the table here, but we are using seeds to load
    our data in this project
    #}
    select * from {{ ref('raw_payments'renamed as (

    select
        id as payment_id,
        order_id,
        payment_method,
        --`amount` is currently stored in cents, so we convert it to dollars
        amount / 100 as amount

    from source

) source

)

select * from renamed
```


## Steps to Reproduce
`sqlfluff fix stg_payments.sql`

## Version
qlffluff: 0.4.1
dbt: 0.19.0
python: 3.7.10

## Configuration
Tested with both
```
[sqlfluff]
templater = jinja
# dbt templating does not keep trailing new lines (L009)
exclude_rules = L009
```
and
```
[sqlfluff]
templater = dbt
# dbt templating does not keep trailing new lines (L009)
exclude_rules = L009
```

## Notes

When the comments from lines 3-6 are remove, `sqlfluff` works as expected.

Hints from issue discussion:
A few minutes after posting this and playing with the above SQL a bit more, I was able to pinpointed the real issue. `sqlfluff` actually has no problems with Jinja comments of the form `{# ... #}`.

It does, however, get lost with the pattern `{#- ... #}` from above. Removing the dash at the beginning of the comment stops `sqlfluff` from breaking.
This SQL file is straight from dbt's [Jaffle Shop](https://github.com/fishtown-analytics/jaffle_shop) tutorial. I wouldn't have guessed that their style of comment was the reason behind the breakage.

Not sure if a real issue that should be further investigated in the code or just a pattern that should be avoided when writing comments. Feel free to close this issue if needed.
If nothing else we should increase test coverage for the `{#- ... #}` syntax and equivalents. For example it probably won't work with the `L046` rule I just put together for linting whitespace within jinja tags.

That might also highlight more issues with the parsing code too. Thanks for the bug report!
I'll look at this issue if I have time. I just fixed a somewhat similar issue, #1321. As part of this work, it would be cool to enhance SQLFluff's core template processing to detect if the source positions are inaccurate wrt the actual input file. That could provide early detection of bugs, rather than waiting until this results in `sqlfluff fix` corrupting a file.

Commit message: Add "pragma: noqa" for newly "not covered" line

Patch to review:
diff --git a/src/sqlfluff/core/templaters/jinja.py b/src/sqlfluff/core/templaters/jinja.py
index 58b7b49b483..0576f66539a 100644
--- a/src/sqlfluff/core/templaters/jinja.py
+++ b/src/sqlfluff/core/templaters/jinja.py
@@ -326,6 +326,18 @@ def _slice_template(cls, in_str: str) -> Iterator[RawFileSlice]:
             "raw_end": "block",
             "raw_begin": "block",
         }
+
+        # Using Jinja whitespace stripping (e.g. `{%-` or `-%}`) breaks the
+        # position markers between unlexed and lexed file. So let's ignore any
+        # request to do that before lexing, by replacing '-' with '+'
+        #
+        # Note: '+' is the default, so shouldn't really be needed but we
+        # explicitly state that to preserve the space for the missing '-' character
+        # so it looks the same.
+        in_str = in_str.replace("{%-", "{%+")
+        in_str = in_str.replace("-%}", "+%}")
+        in_str = in_str.replace("{#-", "{#+")
+        in_str = in_str.replace("-#}", "+#}")
         # https://jinja.palletsprojects.com/en/2.11.x/api/#jinja2.Environment.lex
         for _, elem_type, raw in env.lex(in_str):
             if elem_type == "data":
diff --git a/src/sqlfluff/core/templaters/python.py b/src/sqlfluff/core/templaters/python.py
index b4c3f89e6b9..fe58e956c9d 100644
--- a/src/sqlfluff/core/templaters/python.py
+++ b/src/sqlfluff/core/templaters/python.py
@@ -934,7 +934,7 @@ def _split_uniques_coalesce_rest(
                         if last_owu_idx is None or last_owu_idx + 1 >= len(
                             int_file_slice.slice_buffer
                         ):
-                            cur_idx = 0
+                            cur_idx = 0  # pragma: noqa
                         else:
                             cur_idx = last_owu_idx + 1
 
diff --git a/test/fixtures/linter/autofix/ansi/015_jinja_leading_whitespace/after.sql b/test/fixtures/linter/autofix/ansi/015_jinja_leading_whitespace/after.sql
new file mode 100644
index 00000000000..f36fe85d2c1
--- /dev/null
+++ b/test/fixtures/linter/autofix/ansi/015_jinja_leading_whitespace/after.sql
@@ -0,0 +1,12 @@
+
+
+{%- set x = 42 %}
+SELECT
+1,
+2;
+
+
+{% set x = 42 %}
+SELECT
+1,
+2
diff --git a/test/fixtures/linter/autofix/ansi/015_jinja_leading_whitespace/before.sql b/test/fixtures/linter/autofix/ansi/015_jinja_leading_whitespace/before.sql
new file mode 100644
index 00000000000..ccb55125178
--- /dev/null
+++ b/test/fixtures/linter/autofix/ansi/015_jinja_leading_whitespace/before.sql
@@ -0,0 +1,8 @@
+
+
+{%- set x = 42 %}
+SELECT 1, 2;
+
+
+{% set x = 42 %}
+SELECT 1, 2
diff --git a/test/fixtures/linter/autofix/ansi/015_jinja_leading_whitespace/test-config.yml b/test/fixtures/linter/autofix/ansi/015_jinja_leading_whitespace/test-config.yml
new file mode 100644
index 00000000000..81de52eae6c
--- /dev/null
+++ b/test/fixtures/linter/autofix/ansi/015_jinja_leading_whitespace/test-config.yml
@@ -0,0 +1,3 @@
+test-config:
+  rules:
+    - L036


Code review comments:
diff_hunk: @@ -934,7 +934,7 @@ def _split_uniques_coalesce_rest(
                         if last_owu_idx is None or last_owu_idx + 1 >= len(
                             int_file_slice.slice_buffer
                         ):
-                            cur_idx = 0
+                            cur_idx = 0  # pragma: noqa
path: src/sqlfluff/core/templaters/python.py
text: @user1:
```suggestion
                            cur_idx = 0  # pragma: no cover
```