Repository: sqlfluff/sqlfluff

Issue:
Add configuration support for implicit table aliasing (Rule L011).
Hi 👋 

I'm working with a team that uses implicit aliasing as their standard practice - and wishes to remove `AS` from table references and joins.

To be able to fully implement SQLFluff as a linter, it would be great to add configuration support to L011 to make this possible.

Here's an example piece of code:

```
-- Snippet 1
SELECT
	a.name
	, b.house
FROM user AS a
JOIN house AS b ON a.house_id = b.id
```

At the moment this would pass L011 checks because AS is explicit.

The code we would like is:

```
-- Snippet 2
SELECT
	a.name
	, b.house
FROM user a
JOIN house b ON a.house_id = b.id
```

In our configuration, we would have to ignore Rule L011 - but we would actually like to use Rule L011 to fix `Snippet 1` in the examples above.

For example

```
-- Snippet 1
SELECT
	a.name
	, b.house
FROM user AS a
JOIN house AS b ON a.house_id = b.id

$ sqlfluff fix Snippet 1

>

-- Snippet 2
SELECT
	a.name
	, b.house
FROM user a
JOIN house b ON a.house_id = b.id
```

It would be great to have a configuration option - for example:

`aliasing: ["implicit", "explicit"]`

Discussion:
codecov[bot]: # [Codecov](https://codecov.io/gh/sqlfluff/sqlfluff/pull/1402?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff) Report
> Merging [#1402](https://codecov.io/gh/sqlfluff/sqlfluff/pull/1402?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff) (4d981bc) 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 `64.42%`.

> :exclamation: Current head 4d981bc differs from pull request most recent head 33c78c4. Consider uploading reports for the commit 33c78c4 to get more accurate results
[![Impacted file tree graph](https://codecov.io/gh/sqlfluff/sqlfluff/pull/1402/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/1402?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff)

```diff
@@             Coverage Diff              @@
##              main    #1402       +/-   ##
============================================
- Coverage   100.00%   62.07%   -37.93%     
============================================
  Files          127      127               
  Lines         8450     8550      +100     
============================================
- Hits          8450     5307     -3143     
- Misses           0     3243     +3243     
```

| 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/1402?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/1402/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/core/rules/config\_info.py](https://codecov.io/gh/sqlfluff/sqlfluff/pull/1402/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff#diff-c3JjL3NxbGZsdWZmL2NvcmUvcnVsZXMvY29uZmlnX2luZm8ucHk=) | `100.00% <ø> (ø)` | |
| [src/sqlfluff/dialects/ansi\_keywords.py](https://codecov.io/gh/sqlfluff/sqlfluff/pull/1402/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/1402/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/1402/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/1402/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/1402/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/1402/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/1402/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/1402/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: |
| ... and [101 more](https://codecov.io/gh/sqlfluff/sqlfluff/pull/1402/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/1402?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/1402?src=pr&el=footer&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff). Last update [6bc0949...33c78c4](https://codecov.io/gh/sqlfluff/sqlfluff/pull/1402?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/CONTRIBUTING.md b/CONTRIBUTING.md
index d18134589..eeb819b7c 100644
--- a/CONTRIBUTING.md
+++ b/CONTRIBUTING.md
@@ -130,6 +130,14 @@ tox -e generate-fixture-yml,cov-init,py38,cov-report,linting
 > generates the results for tests in that Python version and the `cov-report-nobt`
 > environment reports those results out to you (excluding dbt).
 
+You can also run specific tests only by making use of `pytest -k` to match test.
+For example below will only run the tests for rule L012 which is much faster while
+working on an issue, before running full tests at the end:
+
+```
+pytest -k L012 -v
+```
+
 To run the dbt-related tests you will have to explicitly include these tests:
 
 ```shell
diff --git a/src/sqlfluff/core/default_config.cfg b/src/sqlfluff/core/default_config.cfg
index 52e56ccba..b7cb2b5b0 100644
--- a/src/sqlfluff/core/default_config.cfg
+++ b/src/sqlfluff/core/default_config.cfg
@@ -54,6 +54,12 @@ operator_new_lines = after
 [sqlfluff:rules:L010]  # Keywords
 capitalisation_policy = consistent
 
+[sqlfluff:rules:L011]  # Aliasing
+aliasing = explicit
+
+[sqlfluff:rules:L012]  # Aliasing
+aliasing = explicit
+
 [sqlfluff:rules:L014]  # Unquoted identifiers
 extended_capitalisation_policy = consistent
 
diff --git a/src/sqlfluff/core/rules/config_info.py b/src/sqlfluff/core/rules/config_info.py
index 57d0276aa..fc7bc65d9 100644
--- a/src/sqlfluff/core/rules/config_info.py
+++ b/src/sqlfluff/core/rules/config_info.py
@@ -94,6 +94,12 @@ STANDARD_CONFIG_INFO_DICT = {
         "validation": ["before", "after"],
         "definition": ("Should operator be placed before or after newlines."),
     },
+    "aliasing": {
+        "validation": ["implicit", "explicit"],
+        "definition": (
+            "Should alias have an explict AS or is implicit aliasing required?"
+        ),
+    },
 }
 
 
diff --git a/src/sqlfluff/rules/L011.py b/src/sqlfluff/rules/L011.py
index 375da6618..d18cb2ea3 100644
--- a/src/sqlfluff/rules/L011.py
+++ b/src/sqlfluff/rules/L011.py
@@ -8,7 +8,10 @@ from sqlfluff.core.rules.doc_decorators import document_fix_compatible
 
 @document_fix_compatible
 class Rule_L011(BaseRule):
-    """Implicit aliasing of table not allowed. Use explicit `AS` clause.
+    """Implicit/explicit aliasing of table.
+
+    Aliasing of table to follow preference
+    (explicit using an `AS` clause is default).
 
     | **Anti-pattern**
     | In this example, the alias 'voo' is implicit.
@@ -30,6 +33,8 @@ class Rule_L011(BaseRule):
 
     """
 
+    config_keywords = ["aliasing"]
+
     _target_elems = ("from_expression_element",)
 
     def _eval(self, segment, parent_stack, raw_stack, **kwargs):
@@ -41,9 +46,33 @@ class Rule_L011(BaseRule):
         The use of `raw_stack` is just for working out how much whitespace to add.
 
         """
+        fixes = []
+
         if segment.is_type("alias_expression"):
             if parent_stack[-1].is_type(*self._target_elems):
-                if not any(e.name.lower() == "as" for e in segment.segments):
+                if any(e.name.lower() == "as" for e in segment.segments):
+                    if self.aliasing == "implicit":
+                        if segment.segments[0].name.lower() == "as":
+
+                            # Remove the AS as we're using implict aliasing
+                            fixes.append(LintFix("delete", segment.segments[0]))
+                            anchor = raw_stack[-1]
+
+                            # Remove whitespace before (if exists) or after (if not)
+                            if (
+                                len(raw_stack) > 0
+                                and raw_stack[-1].type == "whitespace"
+                            ):
+                                fixes.append(LintFix("delete", raw_stack[-1]))
+                            elif (
+                                len(segment.segments) > 0
+                                and segment.segments[1].type == "whitespace"
+                            ):
+                                fixes.append(LintFix("delete", segment.segments[1]))
+
+                            return LintResult(anchor=anchor, fixes=fixes)
+
+                else:
                     insert_buff = []
 
                     # Add initial whitespace if we need to...
diff --git a/src/sqlfluff/rules/L012.py b/src/sqlfluff/rules/L012.py
index 8e5099863..73060bc35 100644
--- a/src/sqlfluff/rules/L012.py
+++ b/src/sqlfluff/rules/L012.py
@@ -4,7 +4,10 @@ from sqlfluff.rules.L011 import Rule_L011
 
 
 class Rule_L012(Rule_L011):
-    """Implicit aliasing of column not allowed. Use explicit `AS` clause.
+    """Implicit/explicit aliasing of columns.
+
+    Aliasing of columns to follow preference
+    (explicit using an `AS` clause is default).
 
     NB: This rule inherits its functionality from obj:`Rule_L011` but is
     separate so that they can be enabled and disabled separately.
@@ -29,4 +32,6 @@ class Rule_L012(Rule_L011):
 
     """
 
+    config_keywords = ["aliasing"]
+
     _target_elems = ("select_clause_element",)


Test patch:
diff --git a/test/cli/commands_test.py b/test/cli/commands_test.py
index 90a4079fa..eee840f3c 100644
--- a/test/cli/commands_test.py
+++ b/test/cli/commands_test.py
@@ -643,8 +643,7 @@ def test__cli__command_lint_serialize_github_annotation():
                 "test/fixtures/linter/identifier_capitalisation.sql"
             ),
             "line": 3,
-            "message": "L012: Implicit aliasing of column not allowed. Use explicit `AS` "
-            "clause.",
+            "message": "L012: Implicit/explicit aliasing of columns.",
             "start_column": 5,
             "end_column": 5,
             "title": "SQLFluff",
diff --git a/test/fixtures/rules/std_rule_cases/L011.yml b/test/fixtures/rules/std_rule_cases/L011.yml
index 0b6c0bc41..c12b19aec 100644
--- a/test/fixtures/rules/std_rule_cases/L011.yml
+++ b/test/fixtures/rules/std_rule_cases/L011.yml
@@ -4,3 +4,48 @@ test_fail_implicit_alias:
   # Add whitespace when fixing implicit aliasing
   fail_str: select foo.bar from (select 1 as bar)foo
   fix_str: select foo.bar from (select 1 as bar) AS foo
+
+test_fail_implicit_alias_space:
+  # No unnecessary whitespace added when fixing implicit aliasing
+  fail_str: select foo.bar from (select 1 as bar) foo
+  fix_str: select foo.bar from (select 1 as bar) AS foo
+
+test_fail_implicit_alias_explicit:
+  # Test when explicitly setting explict
+  fail_str: select foo.bar from (select 1 as bar) foo
+  fix_str: select foo.bar from (select 1 as bar) AS foo
+  configs:
+    rules:
+      L011:
+        aliasing: explicit
+
+test_fail_implicit_alias_implicit:
+  # Test implicit
+  fail_str: select foo.bar from (select 1 as bar) AS foo
+  fix_str: select foo.bar from (select 1 as bar) foo
+  configs:
+    rules:
+      L011:
+        aliasing: implicit
+
+test_fail_implicit_alias_implicit_multiple:
+  # Test implicit with multiple tables
+  fail_str: select foo.bar from (select 1 as bar) AS bar, (select 1 as foo) AS foo
+  fix_str: select foo.bar from (select 1 as bar) bar, (select 1 as foo) foo
+  configs:
+    rules:
+      L011:
+        aliasing: implicit
+
+test_fail_implicit_alias_implicit_newline:
+  # Add whitespace when fixing implicit aliasing
+  fail_str: |
+      select foo.bar from (select 1 as bar)
+      AS foo
+  fix_str: |
+      select foo.bar from (select 1 as bar)
+      foo
+  configs:
+    rules:
+      L011:
+        aliasing: implicit
diff --git a/test/fixtures/rules/std_rule_cases/L012.yml b/test/fixtures/rules/std_rule_cases/L012.yml
index a40cfb224..14ab4aeba 100644
--- a/test/fixtures/rules/std_rule_cases/L012.yml
+++ b/test/fixtures/rules/std_rule_cases/L012.yml
@@ -8,7 +8,29 @@ issue_561:
         (order by product_position asc) over (partition by (event_id, shelf_position))
       as shelf_catalog_items
     from x
-
   configs:
     core:
-      dialect: snowflake
\ No newline at end of file
+      dialect: snowflake
+
+test_fail_explicit_column_default:
+  # Test explicit column alias
+  fail_str: select 1 bar from table1 b
+  fix_str: select 1 AS bar from table1 b
+
+test_fail_explicit_column_explicit:
+  # Test explicit column alias
+  fail_str: select 1 bar from table1 b
+  fix_str: select 1 AS bar from table1 b
+  configs:
+    rules:
+      L012:
+        aliasing: explicit
+
+test_fail_explicit_column_implicit:
+  # Test explicit column alias
+  fail_str: select 1 AS bar from table1 b
+  fix_str: select 1 bar from table1 b
+  configs:
+    rules:
+      L012:
+        aliasing: implicit
diff --git a/test/rules/std_test.py b/test/rules/std_test.py
index 4ee865677..7b4b9dfae 100644
--- a/test/rules/std_test.py
+++ b/test/rules/std_test.py
@@ -133,6 +133,8 @@ def test__rules__std_file_dbt(rule, path, violations, project_dir):  # noqa
         {"single_table_references": "blah"},
         {"unquoted_identifiers_policy": "blah"},
         {"L010": {"capitalisation_policy": "blah"}},
+        {"L011": {"aliasing": "blah"}},
+        {"L012": {"aliasing": "blah"}},
         {"L014": {"extended_capitalisation_policy": "blah"}},
         {"L030": {"capitalisation_policy": "blah"}},
     ],