Repository: sqlfluff/sqlfluff

Issue:
Trailing commas in select statement should raise linting error
#354 allows parsing of trailing commas in select statements. This is a bigquery feature only (and so technically should raise a *parsing* error), but on balance I think it's more _useful_ that the query parses and then this can be raised as an issue.

I think even in dialects where this is supported, this should be regarded as an anti-pattern. We should have a linting rule which catches this.

Discussion:
codecov[bot]: # [Codecov](https://codecov.io/gh/sqlfluff/sqlfluff/pull/2540?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff) Report
> Merging [#2540](https://codecov.io/gh/sqlfluff/sqlfluff/pull/2540?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff) (4f0343e) into [main](https://codecov.io/gh/sqlfluff/sqlfluff/commit/ef7d86548757fcbc3405ac421c38acefa55db9aa?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff) (ef7d865) will **decrease** coverage by `0.00%`.
> The diff coverage is `96.00%`.

[![Impacted file tree graph](https://codecov.io/gh/sqlfluff/sqlfluff/pull/2540/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/2540?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff)

```diff
@@             Coverage Diff             @@
##              main    #2540      +/-   ##
===========================================
- Coverage   100.00%   99.99%   -0.01%     
===========================================
  Files          162      163       +1     
  Lines        11734    11759      +25     
===========================================
+ Hits         11734    11758      +24     
- Misses           0        1       +1     
```


| [Impacted Files](https://codecov.io/gh/sqlfluff/sqlfluff/pull/2540?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff) | Coverage Δ | |
|---|---|---|
| [src/sqlfluff/core/rules/config\_info.py](https://codecov.io/gh/sqlfluff/sqlfluff/pull/2540/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/rules/L062.py](https://codecov.io/gh/sqlfluff/sqlfluff/pull/2540/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff#diff-c3JjL3NxbGZsdWZmL3J1bGVzL0wwNjIucHk=) | `96.00% <96.00%> (ø)` | |

------

[Continue to review full report at Codecov](https://codecov.io/gh/sqlfluff/sqlfluff/pull/2540?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/2540?src=pr&el=footer&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=sqlfluff). Last update [ef7d865...4f0343e](https://codecov.io/gh/sqlfluff/sqlfluff/pull/2540?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/core/default_config.cfg b/src/sqlfluff/core/default_config.cfg
index 2775cba87..8a9c5c919 100644
--- a/src/sqlfluff/core/default_config.cfg
+++ b/src/sqlfluff/core/default_config.cfg
@@ -143,3 +143,7 @@ unquoted_identifiers_policy = all
 quoted_identifiers_policy = all
 allow_space_in_identifier = False
 additional_allowed_characters = ""
+
+[sqlfluff:rules:L062]
+# Comma separated list of blocked words that should not be used
+blocked_words = None
diff --git a/src/sqlfluff/core/rules/config_info.py b/src/sqlfluff/core/rules/config_info.py
index dc1a33da9..dd70b57c7 100644
--- a/src/sqlfluff/core/rules/config_info.py
+++ b/src/sqlfluff/core/rules/config_info.py
@@ -137,6 +137,12 @@ STANDARD_CONFIG_INFO_DICT = {
             "in addition to alphanumerics (A-Z, a-z, 0-9) and underscores."
         ),
     },
+    "blocked_words": {
+        "definition": (
+            "Optional comma-separated list of blocked words which should not be used "
+            "in statements."
+        ),
+    },
 }
 
 
diff --git a/src/sqlfluff/rules/L062.py b/src/sqlfluff/rules/L062.py
new file mode 100644
index 000000000..5921f15c4
--- /dev/null
+++ b/src/sqlfluff/rules/L062.py
@@ -0,0 +1,91 @@
+"""Implementation of Rule L062."""
+
+from typing import Optional
+
+from sqlfluff.core.rules.base import BaseRule, LintResult, RuleContext
+from sqlfluff.core.rules.doc_decorators import document_configuration
+
+
+@document_configuration
+class Rule_L062(BaseRule):
+    """Block a list of configurable words from being used.
+
+    This generic rule can be useful to prevent certain keywords, functions, or objects
+    from being used. Only whole words can be blocked, not phrases, nor parts of words.
+
+    This block list is case insensitive.
+
+    Example use cases:
+
+    * We prefer ``BOOL`` over ``BOOLEAN`` and there is no existing rule to enforce
+      this. Until such a rule is written, we can add ``BOOLEAN`` to the deny list
+      to cause a linting error to flag this.
+    * We have deprecated a schema/table/function and want to prevent it being used
+      in future. We can add that to the denylist and then add a ``-- noqa: L062`` for
+      the few exceptions that still need to be in the code base for now.
+
+    | **Anti-pattern**
+    | If the ``blocked_words`` config is set to ``deprecated_table,bool`` then the
+    | following will flag:
+
+    .. code-block:: sql
+
+        SELECT * FROM deprecated_table WHERE 1 = 1;
+        CREATE TABLE myschema.t1 (a BOOL);
+
+    | **Best practice**
+    | Do not used any blocked words:
+
+    .. code-block:: sql
+
+        SELECT * FROM another_table WHERE 1 = 1;
+        CREATE TABLE myschema.t1 (a BOOLEAN);
+
+    """
+
+    config_keywords = [
+        "blocked_words",
+    ]
+
+    def _eval(self, context: RuleContext) -> Optional[LintResult]:
+        # Config type hints
+        self.blocked_words: Optional[str]
+
+        # Exit early if no block list set
+        if not self.blocked_words:
+            return None
+
+        # Get the ignore list configuration and cache it
+        try:
+            blocked_words_list = self.blocked_words_list
+        except AttributeError:
+            # First-time only, read the settings from configuration.
+            # So we can cache them for next time for speed.
+            blocked_words_list = self._init_blocked_words()
+
+        # Only look at child elements
+        # Note: we do not need to ignore comments or meta types
+        # or the like as they will not have single word raws
+        if context.segment.segments:
+            return None
+
+        if context.segment.raw_upper in blocked_words_list:
+            return LintResult(
+                anchor=context.segment,
+                description=f"Use of blocked word '{context.segment.raw}'.",
+            )
+
+        return None
+
+    def _init_blocked_words(self):
+        """Called first time rule is evaluated to fetch & cache the blocked_words."""
+        blocked_words_config = getattr(self, "blocked_words")
+        if blocked_words_config:
+            self.blocked_words_list = self.split_comma_separated_string(
+                blocked_words_config.upper()
+            )
+        else:  # pragma: no cover
+            # Shouldn't get here as we exit early if no block list
+            self.blocked_words_list = []
+
+        return self.blocked_words_list


Test patch:
diff --git a/test/fixtures/rules/std_rule_cases/L062.yml b/test/fixtures/rules/std_rule_cases/L062.yml
new file mode 100644
index 000000000..c9ae94eb2
--- /dev/null
+++ b/test/fixtures/rules/std_rule_cases/L062.yml
@@ -0,0 +1,91 @@
+rule: L062
+
+test_pass_default_none:
+  pass_str: |
+    SELECT col1 FROM None
+
+test_fail_deny_word:
+  fail_str: |
+    SELECT col1 FROM deprecated_table
+  configs:
+    rules:
+      L062:
+        blocked_words: deprecated_table
+
+test_fail_deny_word_case_difference1:
+  fail_str: |
+    SELECT col1 FROM deprecated_table
+  configs:
+    rules:
+      L062:
+        blocked_words: Deprecated_Table
+
+test_fail_deny_word_case_difference2:
+  fail_str: |
+    SELECT col1 FROM Deprecated_Table
+  configs:
+    rules:
+      L062:
+        blocked_words: deprecated_table
+
+test_fail_multiple_deny_words1:
+  fail_str: |
+    SELECT myOldFunction(col1) FROM table1
+  configs:
+    rules:
+      L062:
+        blocked_words: deprecated_table,myoldFunction
+
+test_fail_multiple_deny_words2:
+  fail_str: |
+    SELECT col1 FROM deprecated_table
+  configs:
+    rules:
+      L062:
+        blocked_words: deprecated_table,myoldFunction
+
+test_pass_not_complete_match:
+  pass_str: |
+    SELECT col1 FROM deprecated_table1
+  configs:
+    rules:
+      L062:
+        blocked_words: deprecated_table
+
+test_pass_is_comment:
+  pass_str: |
+    -- deprecated_table
+    SELECT col1 FROM new_table
+  configs:
+    rules:
+      L062:
+        blocked_words: deprecated_table
+
+test_pass_in_comment:
+  pass_str: |
+    -- This used to use the deprecated_table
+    SELECT col1 FROM new_table
+  configs:
+    rules:
+      L062:
+        blocked_words: deprecated_table
+
+test_fail_bool:
+  fail_str: |
+    CREATE TABLE myschema.t1 (a BOOL);
+  configs:
+    core:
+      dialect: exasol
+    rules:
+      L062:
+        blocked_words: bool
+
+test_pass_bool:
+  pass_str: |
+    CREATE TABLE myschema.t1 (a BOOLEAN);
+  configs:
+    core:
+      dialect: exasol
+    rules:
+      L062:
+        blocked_words: bool
diff --git a/test/rules/std_L062_test.py b/test/rules/std_L062_test.py
new file mode 100644
index 000000000..a838ad82f
--- /dev/null
+++ b/test/rules/std_L062_test.py
@@ -0,0 +1,20 @@
+"""Tests the python routines within L062."""
+from sqlfluff.core import FluffConfig
+from sqlfluff.core import Linter
+
+
+def test__rules__std_L062_raised() -> None:
+    """L062 is raised for use of blocked words with correct error message."""
+    sql = "SELECT MYOLDFUNCTION(col1) FROM deprecated_table;\n"
+    cfg = FluffConfig()
+    cfg.set_value(
+        config_path=["rules", "L062", "blocked_words"],
+        val="myoldfunction,deprecated_table",
+    )
+    linter = Linter(config=cfg)
+    result_records = linter.lint_string_wrapped(sql).as_records()
+    result = result_records[0]["violations"]
+
+    assert len(result) == 2
+    assert result[0]["description"] == "Use of blocked word 'MYOLDFUNCTION'."
+    assert result[1]["description"] == "Use of blocked word 'deprecated_table'."