Repository: sqlfluff/sqlfluff
Pull request: Allow optional enforcing of implicit aliasing of tables (L011) and columns (L012)
### Brief summary of the change made
Fixes #1400 

### Are there any other side effects of this change that we should be aware of?
Affects rules L011 and L012 which inherits from this. Default is left as explicit.

### 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:
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"]`

Hints from issue discussion:

Commit message: Merge branch 'main' into allow-implicit-aliasing

Patch to review:
diff --git a/CONTRIBUTING.md b/CONTRIBUTING.md
index adba75a5b46..1525c3028ea 100644
--- a/CONTRIBUTING.md
+++ b/CONTRIBUTING.md
@@ -101,6 +101,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 52e56ccbab5..b7cb2b5b060 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 57d0276aa64..816de378d02 100644
--- a/src/sqlfluff/core/rules/config_info.py
+++ b/src/sqlfluff/core/rules/config_info.py
@@ -94,6 +94,12 @@
         "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 allowed?"
+        ),
+    },
 }
 
 
diff --git a/src/sqlfluff/rules/L011.py b/src/sqlfluff/rules/L011.py
index 375da66180c..d18cb2ea39e 100644
--- a/src/sqlfluff/rules/L011.py
+++ b/src/sqlfluff/rules/L011.py
@@ -8,7 +8,10 @@
 
 @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 @@ def _eval(self, segment, parent_stack, raw_stack, **kwargs):
         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 8e5099863a0..73060bc35fa 100644
--- a/src/sqlfluff/rules/L012.py
+++ b/src/sqlfluff/rules/L012.py
@@ -4,7 +4,10 @@
 
 
 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",)
diff --git a/test/cli/commands_test.py b/test/cli/commands_test.py
index 90a4079fa70..eee840f3c86 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 0b6c0bc41b0..c12b19aec15 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 a40cfb22436..8378a29c26b 100644
--- a/test/fixtures/rules/std_rule_cases/L012.yml
+++ b/test/fixtures/rules/std_rule_cases/L012.yml
@@ -11,4 +11,29 @@ issue_561:
 
   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 4ee86567737..7b4b9dfae08 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"}},
     ],


Code review comments:
diff_hunk: @@ -94,6 +94,12 @@
         "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 allowed?"
path: src/sqlfluff/core/rules/config_info.py
text: @user1:
```suggestion
            "Should alias have an explict AS or is implicit aliasing required?"
```