Repository: sqlfluff/sqlfluff

Issue:
Support `COUNT(0)` as well as `COUNT(1)`
L047 allows enforcing consistency of use of `COUNT(*)` or `COUNT(1)`.

However some teams use `COUNT(0)` instead of either. Personally `COUNT(1)` has always made more sense to me, but that's by the by as sometimes you have to swallow your own preferences to follow team style.

So I would like to add the option to be able to enforce `COUNT(0)`. I could see this being done in two ways:
- The easiest would be to add a `prefer_count_0` flag similar to `prefer_count_1`, and then decide a precedence if both are set (I'd suggest `prefer_count_1` for backwards compatibility).
- Perhaps a cleaner way (in terms of config anyway) would be to to change this to a `prefer_count="1"` option, that could also be set to `"0"`, though that would be a breaking change (unless you supported both - in which case you might as well just go with the first option).

Any thoughts on this?

Happy to tackle this one myself btw.

Discussion:

Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index cd7b7192e..e82ee8ca0 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -10,13 +10,14 @@ and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0
 
 - Support for primary index name, collect stats improvement, COMMENT statement for teradata dialect [#] (https://github.com/sqlfluff/sqlfluff/issues/)
 - Support config for L007 to prefer end of line operators [#1261](https://github.com/sqlfluff/sqlfluff/issues/1261)
-
-### Changed
-
 - Support for DETERMINISTIC user defined functions in BigQuery dialect [#1251](https://github.com/sqlfluff/sqlfluff/issues/1251)
 - Support more identifiers in BigQuery dialect [#1253](https://github.com/sqlfluff/sqlfluff/issues/1253)
 - Support function member field references in BigQuery dialect [#1255](https://github.com/sqlfluff/sqlfluff/issues/1255)
 - Support alternative indentation for USING and ON clauses [#1250](https://github.com/sqlfluff/sqlfluff/issues/1250)
+- Support COUNT(0) preference over COUNT(*) or COUNT(1) [#1260](https://github.com/sqlfluff/sqlfluff/issues/1260)
+
+### Changed
+
 - Fix comma removed by L019 [#939](https://github.com/sqlfluff/sqlfluff/issues/939)
 - Update L019 (leading/trailng comma rule) so it doesn't run on unparsable code.
 - The "--nocolor" command-line option should suppress emoji output (https://github.com/sqlfluff/sqlfluff/issues/1246)
diff --git a/src/sqlfluff/core/default_config.cfg b/src/sqlfluff/core/default_config.cfg
index 87290b284..52e56ccba 100644
--- a/src/sqlfluff/core/default_config.cfg
+++ b/src/sqlfluff/core/default_config.cfg
@@ -78,3 +78,4 @@ forbid_subquery_in = join
 
 [sqlfluff:rules:L047]  # Consistent syntax to count all rows
 prefer_count_1 = False
+prefer_count_0 = False
diff --git a/src/sqlfluff/core/rules/config_info.py b/src/sqlfluff/core/rules/config_info.py
index 89c0307ff..57d0276aa 100644
--- a/src/sqlfluff/core/rules/config_info.py
+++ b/src/sqlfluff/core/rules/config_info.py
@@ -84,7 +84,11 @@ STANDARD_CONFIG_INFO_DICT = {
     },
     "prefer_count_1": {
         "validation": [True, False],
-        "definition": ("Should count(1) be preferred over count(*)?"),
+        "definition": ("Should count(1) be preferred over count(*) and count(0)?"),
+    },
+    "prefer_count_0": {
+        "validation": [True, False],
+        "definition": ("Should count(0) be preferred over count(*) and count(1)?"),
     },
     "operator_new_lines": {
         "validation": ["before", "after"],
diff --git a/src/sqlfluff/rules/L047.py b/src/sqlfluff/rules/L047.py
index 7f4df9a17..7e2a4b86e 100644
--- a/src/sqlfluff/rules/L047.py
+++ b/src/sqlfluff/rules/L047.py
@@ -12,8 +12,12 @@ from sqlfluff.core.rules.doc_decorators import (
 class Rule_L047(BaseRule):
     """Use consistent syntax to express "count number of rows".
 
-    COUNT(*) and COUNT(1) are equivalent syntaxes in many SQL engines
-    due to optimizers interpreting these instructions as
+    Note:
+        If both `prefer_count_1` and `prefer_count_0` are set to true
+        then `prefer_count_1` has precedence.
+
+    COUNT(*), COUNT(1), and even COUNT(0) are equivalent syntaxes in many SQL
+    engines due to optimizers interpreting these instructions as
     "count number of rows in result".
 
     The ANSI-92_ spec mentions the COUNT(*) syntax specifically as
@@ -24,8 +28,9 @@ class Rule_L047(BaseRule):
 
     So by default, SQLFluff enforces the consistent use of COUNT(*).
 
-    If the SQL engine you work with, or your team, prefers COUNT(1)
-    over COUNT(*) you can configure this rule to consistently enforce COUNT(1).
+    If the SQL engine you work with, or your team, prefers COUNT(1) or COUNT(0)
+    over COUNT(*) you can configure this rule to consistently enforce your
+    preference.
 
     .. _ANSI-92: http://msdn.microsoft.com/en-us/library/ms175997.aspx
 
@@ -38,7 +43,8 @@ class Rule_L047(BaseRule):
         from table_a
 
     | **Best practice**
-    |   Use count(*) unless specified otherwise by config ``prefer_count_1``
+    | Use ``count(*)`` unless specified otherwise by config ``prefer_count_1``,
+    | or ``prefer_count_0`` as preferred.
 
     .. code-block:: sql
 
@@ -48,7 +54,7 @@ class Rule_L047(BaseRule):
 
     """
 
-    config_keywords = ["prefer_count_1"]
+    config_keywords = ["prefer_count_1", "prefer_count_0"]
 
     def _eval(self, segment, **kwargs):
         """Find rule violations and provide fixes."""
@@ -76,25 +82,36 @@ class Rule_L047(BaseRule):
             if len(f_content) != 1:
                 return None
 
-            if self.prefer_count_1 and f_content[0].is_type("star"):
+            preferred = "*"
+            if self.prefer_count_1:
+                preferred = "1"
+            elif self.prefer_count_0:
+                preferred = "0"
+
+            if f_content[0].is_type("star") and (
+                self.prefer_count_1 or self.prefer_count_0
+            ):
                 return LintResult(
                     anchor=segment,
                     fixes=[
                         LintFix(
                             "edit",
                             f_content[0],
-                            f_content[0].edit(f_content[0].raw.replace("*", "1")),
+                            f_content[0].edit(f_content[0].raw.replace("*", preferred)),
                         ),
                     ],
                 )
-            if not self.prefer_count_1 and f_content[0].is_type("expression"):
+
+            if f_content[0].is_type("expression"):
                 expression_content = [
                     seg for seg in f_content[0].segments if not seg.is_meta
                 ]
+
                 if (
                     len(expression_content) == 1
                     and expression_content[0].is_type("literal")
-                    and expression_content[0].raw == "1"
+                    and expression_content[0].raw in ["0", "1"]
+                    and expression_content[0].raw != preferred
                 ):
                     return LintResult(
                         anchor=segment,
@@ -103,7 +120,9 @@ class Rule_L047(BaseRule):
                                 "edit",
                                 expression_content[0],
                                 expression_content[0].edit(
-                                    expression_content[0].raw.replace("1", "*")
+                                    expression_content[0].raw.replace(
+                                        expression_content[0].raw, preferred
+                                    )
                                 ),
                             ),
                         ],


Test patch:
diff --git a/test/fixtures/rules/std_rule_cases/L047.yml b/test/fixtures/rules/std_rule_cases/L047.yml
index 7f0810c70..3fd8f187a 100644
--- a/test/fixtures/rules/std_rule_cases/L047.yml
+++ b/test/fixtures/rules/std_rule_cases/L047.yml
@@ -23,6 +23,71 @@ passes_on_count_1:
       L047:
         prefer_count_1: True
 
+changes_count_0_to_count_star:
+  fail_str: |
+    select
+        foo,
+        count(0)
+    from my_table
+    group by
+      foo
+
+  fix_str: |
+    select
+        foo,
+        count(*)
+    from my_table
+    group by
+      foo
+
+passes_on_count_0:
+  pass_str: |
+    select
+        foo,
+        count(0)
+    from my_table
+    group by
+      foo
+
+  configs: &prefer_count_0
+    rules:
+      L047:
+        prefer_count_0: True
+
+passes_on_count_1_if_both_present:
+  pass_str: |
+    select
+        foo,
+        count(1)
+    from my_table
+    group by
+      foo
+
+  configs: &prefer_both
+    rules:
+      L047:
+        prefer_count_0: True
+        prefer_count_1: True
+
+changes_to_count_1_if_both_present:
+  fail_str: |
+    select
+        foo,
+        count(*)
+    from my_table
+    group by
+      foo
+
+  fix_str: |
+    select
+        foo,
+        count(1)
+    from my_table
+    group by
+      foo
+
+  configs: *prefer_both
+
 changes_count_1_to_count_star:
   fail_str: |
     select
@@ -57,6 +122,25 @@ handles_whitespaces:
     group by
       foo
 
+changes_count_star_to_count_0:
+  fail_str: |
+    select
+        foo,
+        count(*)
+    from my_table
+    group by
+      foo
+
+  fix_str: |
+    select
+        foo,
+        count(0)
+    from my_table
+    group by
+      foo
+
+  configs: *prefer_count_0
+
 changes_count_star_to_count_1:
   fail_str: |
     select
@@ -76,6 +160,44 @@ changes_count_star_to_count_1:
 
   configs: *prefer_count_1
 
+changes_count_1_to_count_0:
+  fail_str: |
+    select
+        foo,
+        count(1)
+    from my_table
+    group by
+      foo
+
+  fix_str: |
+    select
+        foo,
+        count(0)
+    from my_table
+    group by
+      foo
+
+  configs: *prefer_count_0
+
+changes_count_0_to_count_1:
+  fail_str: |
+    select
+        foo,
+        count(0)
+    from my_table
+    group by
+      foo
+
+  fix_str: |
+    select
+        foo,
+        count(1)
+    from my_table
+    group by
+      foo
+
+  configs: *prefer_count_1
+
 changes_count_star_to_count_1_handle_new_line:
   fail_str: |
     select