Repository: sqlfluff/sqlfluff

Issue:
L014 Inserting underscores after numbers in unquoted identifiers
## Expected Behaviour

Only casing of unquoted identifiers is affected by L014, with no other modifications, eg:

Running `sqlfluff fix --rules L014 test_file.sql` on sql script:

```
select a from b;

insert into A1B2C3
select A from B;
```

should produce sql:

```
select a from b;

insert into a1b2c3
select a from b;
```

## Observed Behaviour

Underscores are inserted after numbers in unquoted identifiers when a casing change is applied. EG.

`sqlfluff fix --rules L014 test_file.sql` 

on

```
select a from b;

insert into A1B2C3
select A from B;
```

produces

```
select a from b;

insert into a1_b2_c3
select a from b;
```

## Steps to Reproduce

1. Create a `test_file.sql` containing the sql in the example above.
2. Run `sqlfluff fix --rules L014 test_file.sql`

## Dialect

## Version
Include the output of `sqlfluff --version` along with your Python version

```
sqlfluff, version 0.6.4
Python 3.9.6
```

## Configuration
```
[tool.sqlfluff.core]
templater = "jinja"
sql_file_exts = ".sql,.sql.j2,.dml,.ddl"

[tool.sqlfluff.indentation]
indented_joins = false
indented_using_on = true

[sqlfluff:rules]
tab_space_size = 4
max_line_length = 88
indent_unit = space
comma_style = leading

```


Discussion:
tunetheweb: See also this comment where some of these rules were decided: https://github.com/sqlfluff/sqlfluff/issues/820#issuecomment-918226281
tunetheweb: FYI: @panasenco 
WittierDinosaur: I agree with most of your comments, barring one. https://github.com/sqlfluff/sqlfluff/issues/820#issuecomment-918238258

tunetheweb: > I agree with most of your comments, barring one. [#820 (comment)](https://github.com/sqlfluff/sqlfluff/issues/820#issuecomment-918238258)

We've discussed on that thread and explained my reasoning, and think we've agreed now on what's been implemented here. So all good to review.

But thanks for looking and challenging! Always good to hear opinions and thrash things out to make sure what's being proposed is correct.
tunetheweb: > Looks reasonable to me, but I'm not the most qualified person for the casing stuff. Think we should tag the original author of this work? IIRC, it was contributed about 4 months ago, so they may still be available.

See the comment two above this 😁

There was a robust conversation with @WittierDinosaur on this in that other thread and I eventually managed to persuade him round to what we have here. Will give it a day or two to see if any more comments and, if not, merge.
barrywhart: @tunetheweb: Ah yes, I see some of the earlier discussion now. I'm 👍 for not adding/removing underscores due to the danger of breaking previously working code. I'll throw out there, one option that I don't think y'all considered would be to _warn_ about non-complying code but not provide automatic **fixes** if it would involve adding/removing underscores or something dangerous like that. No strong feelings about whether to do this. Possibly a future option depending on other reviewers' thoughts and/or the eventual user feedback.
codecov[bot]: # [Codecov](https://codecov.io/gh/sqlfluff/sqlfluff/pull/1396?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff) Report
> Merging [#1396](https://codecov.io/gh/sqlfluff/sqlfluff/pull/1396?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff) (105994b) into [main](https://codecov.io/gh/sqlfluff/sqlfluff/commit/63a6120ee8f55bdb10ead32e64b9dc5b4500728f?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff) (63a6120) will **decrease** coverage by `37.92%`.
> The diff coverage is `67.15%`.

[![Impacted file tree graph](https://codecov.io/gh/sqlfluff/sqlfluff/pull/1396/graphs/tree.svg?width=650&height=150&src=pr&token=maM8kLgCmn&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff)](https://codecov.io/gh/sqlfluff/sqlfluff/pull/1396?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff)

```diff
@@             Coverage Diff              @@
##              main    #1396       +/-   ##
============================================
- Coverage   100.00%   62.07%   -37.93%     
============================================
  Files          127      127               
  Lines         8450     8535       +85     
============================================
- Hits          8450     5298     -3152     
- Misses           0     3237     +3237     
```

| Flag | Coverage Δ | |
|---|---|---|
| dbt018-py38 | `?` | |
| dbt019-py38 | `?` | |
| dbt020-py38 | `?` | |
| py36 | `?` | |
| py37 | `?` | |
| py38 | `?` | |
| py39 | `?` | |

Flags with carried forward coverage won't be shown. [Click here](https://docs.codecov.io/docs/carryforward-flags?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff#carryforward-flags-in-the-pull-request-comment) to find out more.

| [Impacted Files](https://codecov.io/gh/sqlfluff/sqlfluff/pull/1396?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff) | Coverage Δ | |
|---|---|---|
| [src/sqlfluff/cli/commands.py](https://codecov.io/gh/sqlfluff/sqlfluff/pull/1396/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff#diff-c3JjL3NxbGZsdWZmL2NsaS9jb21tYW5kcy5weQ==) | `26.02% <0.00%> (-73.98%)` | :arrow_down: |
| [src/sqlfluff/dialects/ansi\_keywords.py](https://codecov.io/gh/sqlfluff/sqlfluff/pull/1396/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff#diff-c3JjL3NxbGZsdWZmL2RpYWxlY3RzL2Fuc2lfa2V5d29yZHMucHk=) | `100.00% <ø> (ø)` | |
| [src/sqlfluff/dialects/dialect\_exasol.py](https://codecov.io/gh/sqlfluff/sqlfluff/pull/1396/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff#diff-c3JjL3NxbGZsdWZmL2RpYWxlY3RzL2RpYWxlY3RfZXhhc29sLnB5) | `0.00% <0.00%> (-100.00%)` | :arrow_down: |
| [src/sqlfluff/dialects/dialect\_teradata.py](https://codecov.io/gh/sqlfluff/sqlfluff/pull/1396/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff#diff-c3JjL3NxbGZsdWZmL2RpYWxlY3RzL2RpYWxlY3RfdGVyYWRhdGEucHk=) | `0.00% <0.00%> (-100.00%)` | :arrow_down: |
| [src/sqlfluff/dialects/postgres\_keywords.py](https://codecov.io/gh/sqlfluff/sqlfluff/pull/1396/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff#diff-c3JjL3NxbGZsdWZmL2RpYWxlY3RzL3Bvc3RncmVzX2tleXdvcmRzLnB5) | `95.65% <ø> (-4.35%)` | :arrow_down: |
| [src/sqlfluff/rules/L016.py](https://codecov.io/gh/sqlfluff/sqlfluff/pull/1396/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff#diff-c3JjL3NxbGZsdWZmL3J1bGVzL0wwMTYucHk=) | `73.09% <0.00%> (-26.91%)` | :arrow_down: |
| [src/sqlfluff/rules/L019.py](https://codecov.io/gh/sqlfluff/sqlfluff/pull/1396/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff#diff-c3JjL3NxbGZsdWZmL3J1bGVzL0wwMTkucHk=) | `42.37% <0.00%> (-57.63%)` | :arrow_down: |
| [src/sqlfluff/rules/L030.py](https://codecov.io/gh/sqlfluff/sqlfluff/pull/1396/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff#diff-c3JjL3NxbGZsdWZmL3J1bGVzL0wwMzAucHk=) | `90.00% <ø> (-10.00%)` | :arrow_down: |
| [src/sqlfluff/rules/L003.py](https://codecov.io/gh/sqlfluff/sqlfluff/pull/1396/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff#diff-c3JjL3NxbGZsdWZmL3J1bGVzL0wwMDMucHk=) | `74.63% <31.25%> (-25.37%)` | :arrow_down: |
| [src/sqlfluff/rules/L034.py](https://codecov.io/gh/sqlfluff/sqlfluff/pull/1396/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff#diff-c3JjL3NxbGZsdWZmL3J1bGVzL0wwMzQucHk=) | `89.79% <60.00%> (-10.21%)` | :arrow_down: |
| ... and [100 more](https://codecov.io/gh/sqlfluff/sqlfluff/pull/1396/diff?src=pr&el=tree-more&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff) | |

------

[Continue to review full report at Codecov](https://codecov.io/gh/sqlfluff/sqlfluff/pull/1396?src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/sqlfluff/sqlfluff/pull/1396?src=pr&el=footer&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff). Last update [6bc0949...105994b](https://codecov.io/gh/sqlfluff/sqlfluff/pull/1396?src=pr&el=lastupdated&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff).


Patch:
diff --git a/src/sqlfluff/rules/L010.py b/src/sqlfluff/rules/L010.py
index e73b9bd74..8392943a5 100644
--- a/src/sqlfluff/rules/L010.py
+++ b/src/sqlfluff/rules/L010.py
@@ -126,15 +126,10 @@ class Rule_L010(BaseRule):
                     f"Setting concrete policy '{concrete_policy}' from cap_policy"
                 )
 
+        # Set the fixed to same as initial in case any of below don't match
+        fixed_raw = segment.raw
         # We need to change the segment to match the concrete policy
         if concrete_policy in ["upper", "lower", "capitalise"]:
-            if "pascal" in cap_policy_opts and segment.raw[0].isupper():
-                # Insert undescores in transitions between lower and upper
-                fixed_raw = re.sub("(?<=[a-z0-9])(?=[A-Z])", "_", segment.raw)
-                self.logger.debug(f"Inserted underscores: {fixed_raw}")
-            else:
-                fixed_raw = segment.raw
-
             if concrete_policy == "upper":
                 fixed_raw = fixed_raw.upper()
             elif concrete_policy == "lower":
@@ -142,9 +137,14 @@ class Rule_L010(BaseRule):
             elif concrete_policy == "capitalise":
                 fixed_raw = fixed_raw.capitalize()
         elif concrete_policy == "pascal":
+            # For Pascal we set the first letter in each "word" to uppercase
+            # We do not lowercase other letters to allow for PascalCase style
+            # words. This does mean we allow all UPPERCASE and also don't
+            # correct Pascalcase to PascalCase, but there's only so much we can
+            # do. We do correct underscore_words to Underscore_Words.
             fixed_raw = re.sub(
                 "([^a-zA-Z0-9]+|^)([a-zA-Z0-9])([a-zA-Z0-9]*)",
-                lambda match: match.group(2).upper() + match.group(3).lower(),
+                lambda match: match.group(1) + match.group(2).upper() + match.group(3),
                 segment.raw,
             )
 


Test patch:
diff --git a/test/fixtures/rules/std_rule_cases/L014.yml b/test/fixtures/rules/std_rule_cases/L014.yml
index 8b78e4e67..e6d22905b 100644
--- a/test/fixtures/rules/std_rule_cases/L014.yml
+++ b/test/fixtures/rules/std_rule_cases/L014.yml
@@ -22,6 +22,10 @@ test_pass_consistent_capitalisation_with_single_word_pascal:
   # Single-word ambiguity: Pascal vs Capitalise
   pass_str: SELECT AppleFritter, Banana
 
+test_pass_consistent_capitalisation_with_multiple_words_with_numbers:
+  # Numbers count as part of words so following letter can be upper or lower
+  pass_str: SELECT AppleFritter, Apple123fritter, Apple123Fritter
+
 test_fail_inconsistent_capitalisation_1:
   # Test that fixes are consistent
   fail_str: SELECT a,   B
@@ -49,7 +53,7 @@ test_pass_consistent_capitalisation_policy_pascal_2:
 
 test_pass_consistent_capitalisation_policy_pascal_3:
   fail_str: SELECT pascalCase
-  fix_str: SELECT Pascalcase
+  fix_str: SELECT PascalCase
   configs:
     rules:
       L014:
@@ -70,6 +74,10 @@ test_pass_consistent_capitalisation_policy_pascal_5:
         extended_capitalisation_policy: pascal
 
 test_pass_consistent_capitalisation_policy_pascal_6:
+  # This could be argued as not PascalCase but technically it is
+  # unless we introduce a full dictionarry of words to recognise
+  # where word breaks are (an impossible task!). Also what about
+  # abbreviations (e.g. NASA)?
   pass_str: SELECT PASCALCASE 
   configs:
     rules:
@@ -86,15 +94,17 @@ test_fail_inconsistent_capitalisation_policy_pascal_1:
 
 test_fail_inconsistent_capitalisation_policy_pascal_2:
   fail_str: SELECT pascal_case
-  fix_str: SELECT PascalCase
+  fix_str: SELECT Pascal_Case
   configs:
     rules:
       L014:
         extended_capitalisation_policy: pascal
 
 test_fail_inconsistent_capitalisation_policy_pascal_3:
-  fail_str: SELECT PASCAL_CASE
-  fix_str: SELECT PascalCase
+  # Similar to above, you could argue the fixed string is
+  # Not really Pascal Case, but it's closer than it was!
+  fail_str: SELECT pASCAL_CASE
+  fix_str: SELECT PASCAL_CASE
   configs:
     rules:
       L014:
@@ -102,7 +112,7 @@ test_fail_inconsistent_capitalisation_policy_pascal_3:
 
 test_fail_inconsistent_capitalisation_policy_pascal_4:
   fail_str: SELECT PasCalCase
-  fix_str: SELECT pas_cal_case
+  fix_str: SELECT pascalcase
   configs:
     rules:
       L014:
@@ -110,7 +120,7 @@ test_fail_inconsistent_capitalisation_policy_pascal_4:
 
 test_fail_consistent_capitalisation_policy_pascal_5:
   fail_str: SELECT PascalCaseNAME
-  fix_str: SELECT PASCAL_CASE_NAME
+  fix_str: SELECT PASCALCASENAME
   configs:
     rules:
       L014:
@@ -119,7 +129,7 @@ test_fail_consistent_capitalisation_policy_pascal_5:
 test_fail_inconsistent_capitalisation_1:
   # Pascal vs Capitalise
   fail_str: SELECT AppleFritter, Banana_split
-  fix_str: SELECT AppleFritter, BananaSplit
+  fix_str: SELECT AppleFritter, Banana_Split
 
 test_pass_policy_unquoted_identifiers_aliases_1:
   pass_str: SELECT a,   B
@@ -136,6 +146,8 @@ test_pass_policy_unquoted_identifiers_aliases_2:
         unquoted_identifiers_policy: aliases
 
 test_pass_policy_unquoted_identifiers_aliases_3:
+  # See above commentsin regards to whether this should
+  # really be considered PascalCase (we treat as yes)
   pass_str: SELECT PASCAL_CASE
   configs:
     rules:
@@ -152,7 +164,7 @@ test_pass_policy_unquoted_identifiers_aliases_4:
 
 test_policy_unquoted_identifiers_aliases_5:
   fail_str: SELECT UPPER_CASE AS PascalCase, PascalCase AS lower_case
-  fix_str: SELECT UPPER_CASE AS PascalCase, PascalCase AS LowerCase
+  fix_str: SELECT UPPER_CASE AS PascalCase, PascalCase AS Lower_Case
   configs:
     rules:
       L014:
@@ -160,7 +172,7 @@ test_policy_unquoted_identifiers_aliases_5:
 
 test_policy_unquoted_identifiers_aliases_6:
   fail_str: SELECT UPPER_CASE AS PascalCase, PascalCase AS lower_case
-  fix_str: SELECT UPPER_CASE AS PASCAL_CASE, PascalCase AS LOWER_CASE
+  fix_str: SELECT UPPER_CASE AS PASCALCASE, PascalCase AS LOWER_CASE
   configs:
     rules:
       L014:
@@ -177,7 +189,7 @@ test_policy_unquoted_identifiers_aliases_7:
 
 test_policy_unquoted_identifiers_aliases_8:
   fail_str: SELECT UPPER_CASE AS PascalCase, PascalCase AS lower_case FROM lower_case AS lower_case
-  fix_str: SELECT UPPER_CASE AS PascalCase, PascalCase AS LowerCase FROM lower_case AS LowerCase
+  fix_str: SELECT UPPER_CASE AS PascalCase, PascalCase AS Lower_Case FROM lower_case AS Lower_Case
   configs:
     rules:
       L014:
@@ -192,7 +204,7 @@ test_policy_unquoted_identifiers_column_aliases_1:
 
 test_policy_unquoted_identifiers_aliases_2:
   fail_str: SELECT UPPER_CASE AS PascalCase, PascalCase AS lower_case FROM lower_case AS lower_case
-  fix_str: SELECT UPPER_CASE AS PascalCase, PascalCase AS LowerCase FROM lower_case AS lower_case
+  fix_str: SELECT UPPER_CASE AS PascalCase, PascalCase AS Lower_Case FROM lower_case AS lower_case
   configs:
     rules:
       L014: