Repository: sqlfluff/sqlfluff
Pull request: Support COUNT(0) preference over COUNT(*) or COUNT(1)
Fixes #1260 

I've gone with the simpler option of two separate configs to avoid breaking backwards compatibility. So you can have:

```yml
prefer_count_1: True
```

or:

```yml
prefer_count_0: True
```

And if both are specified:
```yml
prefer_count_1: True
prefer_count_0: True
```

Then `prefer_count_1` takes precedence (no matter what order they are specified in).

As I say, ideally you'd not be able to have contradictory params like this, so let me know if you think we should introduce a breaking change here instead.

Barry

P.S. I swear I'm not trying to annoy you @barrywhart - I just seem to get each next PR finished just at the exact same time as you merge the last one! 😁

Problem statement:
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.

Hints from issue discussion:

Commit message: Update src/sqlfluff/rules/L047.py

Co-authored-by: Barry Hart <barrywhart@yahoo.com>

Patch to review:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index e01391e3822..a20ac0026b6 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -13,6 +13,7 @@ and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0
 - 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)
 
 ## [0.6.2] - 2021-07-22
 ### Added
diff --git a/src/sqlfluff/core/default_config.cfg b/src/sqlfluff/core/default_config.cfg
index fb514f5b28f..6ec01d6c6a1 100644
--- a/src/sqlfluff/core/default_config.cfg
+++ b/src/sqlfluff/core/default_config.cfg
@@ -75,3 +75,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 78fedcd3f0b..fc5af688aaf 100644
--- a/src/sqlfluff/core/rules/config_info.py
+++ b/src/sqlfluff/core/rules/config_info.py
@@ -84,7 +84,11 @@
     },
     "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)?"),
     },
 }
 
diff --git a/src/sqlfluff/rules/L047.py b/src/sqlfluff/rules/L047.py
index 7f4df9a1786..3cf97b8c780 100644
--- a/src/sqlfluff/rules/L047.py
+++ b/src/sqlfluff/rules/L047.py
@@ -12,8 +12,12 @@
 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."""
@@ -87,7 +93,64 @@ def _eval(self, segment, **kwargs):
                         ),
                     ],
                 )
-            if not self.prefer_count_1 and f_content[0].is_type("expression"):
+            elif self.prefer_count_1 and 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 == "0"
+                ):
+                    return LintResult(
+                        anchor=segment,
+                        fixes=[
+                            LintFix(
+                                "edit",
+                                expression_content[0],
+                                expression_content[0].edit(
+                                    expression_content[0].raw.replace("0", "1")
+                                ),
+                            ),
+                        ],
+                    )
+            elif self.prefer_count_0 and f_content[0].is_type("star"):
+                return LintResult(
+                    anchor=segment,
+                    fixes=[
+                        LintFix(
+                            "edit",
+                            f_content[0],
+                            f_content[0].edit(f_content[0].raw.replace("*", "0")),
+                        ),
+                    ],
+                )
+            elif self.prefer_count_0 and 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"
+                ):
+                    return LintResult(
+                        anchor=segment,
+                        fixes=[
+                            LintFix(
+                                "edit",
+                                expression_content[0],
+                                expression_content[0].edit(
+                                    expression_content[0].raw.replace("1", "0")
+                                ),
+                            ),
+                        ],
+                    )
+            elif (
+                not self.prefer_count_1
+                and not self.prefer_count_0
+                and f_content[0].is_type("expression")
+            ):
                 expression_content = [
                     seg for seg in f_content[0].segments if not seg.is_meta
                 ]
@@ -108,3 +171,20 @@ def _eval(self, segment, **kwargs):
                             ),
                         ],
                     )
+                elif (
+                    len(expression_content) == 1
+                    and expression_content[0].is_type("literal")
+                    and expression_content[0].raw == "0"
+                ):
+                    return LintResult(
+                        anchor=segment,
+                        fixes=[
+                            LintFix(
+                                "edit",
+                                expression_content[0],
+                                expression_content[0].edit(
+                                    expression_content[0].raw.replace("0", "*")
+                                ),
+                            ),
+                        ],
+                    )
diff --git a/test/fixtures/rules/std_rule_cases/L047.yml b/test/fixtures/rules/std_rule_cases/L047.yml
index 7f0810c7080..3fd8f187a38 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


Code review comments:
diff_hunk: @@ -87,7 +93,64 @@ def _eval(self, segment, **kwargs):
                         ),
                     ],
                 )
-            if not self.prefer_count_1 and f_content[0].is_type("expression"):
+            elif self.prefer_count_1 and f_content[0].is_type("expression"):
path: src/sqlfluff/rules/L047.py
text: @user1:
Looking at this code, it seems pretty repetitive now -- i.e. the code for `count(0)` and `count(1)` is often identical. Do you think we could DRY this up (i.e. https://en.wikipedia.org/wiki/Don%27t_repeat_yourself)?

@author:
You were right. This could be simplified - a lot!