Repository: sqlfluff/sqlfluff

Issue:
L013 and L025 bug
```
select
    ps.*,
    pandgs.blah,
    pandgs.blah1,
    pandgs.blah2,
    pandgs.blah3
from asdf as ps
left join fdsa as pandgs using (moo)

```

results in 
```
L:   2 | P:   5 | L013 | Column expression without alias. Use explicit `AS`
                       | clause.
L:   7 | P:  14 | L025 | Alias 'ps' is never used in SELECT statement.
```

Discussion:

Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 459b14070..20390e176 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -16,6 +16,10 @@ and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0
 - Created new Github Organisation (https://github.com/sqlfluff) and
   migrated from https://github.com/alanmcruickshank/sqlfluff to
   https://github.com/sqlfluff/sqlfluff.
+- Changed the handling of `*` and `a.b.*` expressions to have their
+  own expressions. Any dependencies on this structure downstream
+  will be broken. This also fixes the linting of both kinds of expressions
+  with regard to L013 and L025.
 
 ## [0.3.6] - 2020-09-24
 
diff --git a/src/sqlfluff/dialects/dialect_ansi.py b/src/sqlfluff/dialects/dialect_ansi.py
index 86cf0d268..4db5fa8dd 100644
--- a/src/sqlfluff/dialects/dialect_ansi.py
+++ b/src/sqlfluff/dialects/dialect_ansi.py
@@ -93,7 +93,7 @@ ansi_dialect.add(
     EndSquareBracketSegment=KeywordSegment.make(']', name='end_square_bracket', type='end_square_bracket'),
     CommaSegment=KeywordSegment.make(',', name='comma', type='comma'),
     DotSegment=KeywordSegment.make('.', name='dot', type='dot'),
-    StarSegment=KeywordSegment.make('*', name='star'),
+    StarSegment=KeywordSegment.make('*', name='star', type='star'),
     TildeSegment=KeywordSegment.make('~', name='tilde'),
     CastOperatorSegment=KeywordSegment.make('::', name='casting_operator', type='casting_operator'),
     PlusSegment=KeywordSegment.make('+', name='plus', type='binary_operator'),
@@ -246,6 +246,13 @@ class ObjectReferenceSegment(BaseSegment):
         code_only=False
     )
 
+    @staticmethod
+    def _iter_reference_parts(elem):
+        """Extract the elements of a reference and yield."""
+        # trim on quotes and split out any dots.
+        for part in elem.raw_trimmed().split('.'):
+            yield part, elem
+
     def iter_raw_references(self):
         """Generate a list of reference strings and elements.
 
@@ -255,14 +262,29 @@ class ObjectReferenceSegment(BaseSegment):
         """
         # Extract the references from those identifiers (because some may be quoted)
         for elem in self.recursive_crawl('identifier'):
-            # trim on quotes and split out any dots.
-            for part in elem.raw_trimmed().split('.'):
-                yield part, elem
+            yield from self._iter_reference_parts(elem)
 
     def is_qualified(self):
         """Return if there is more than one element to the reference."""
         return len(list(self.iter_raw_references())) > 1
 
+    def qualification(self):
+        """Return the qualification type of this reference."""
+        return 'qualified' if self.is_qualified() else 'unqualified'
+
+    def extract_reference(self, level):
+        """Extract a reference of a given level.
+
+        e.g. level 1 = the object.
+        level 2 = the table
+        level 3 = the schema
+        etc...
+        """
+        refs = list(self.iter_raw_references())
+        if len(refs) >= level:
+            return refs[-level]
+        return None
+
 
 @ansi_dialect.segment()
 class ArrayAccessorSegment(BaseSegment):
@@ -526,9 +548,15 @@ class TableExpressionSegment(BaseSegment):
         return None
 
 
-ansi_dialect.add(
-    # This is a hook point to allow subclassing for other dialects
-    WildcardSelectTargetElementGrammar=Sequence(
+@ansi_dialect.segment()
+class WildcardIdentifierSegment(ObjectReferenceSegment):
+    """Any identifier of the form a.b.*.
+
+    This inherits iter_raw_references from the
+    ObjectReferenceSegment.
+    """
+    type = "wildcard_identifier"
+    match_grammar = Sequence(
         # *, blah.*, blah.blah.*, etc.
         AnyNumberOf(
             Sequence(
@@ -538,8 +566,33 @@ ansi_dialect.add(
             )
         ),
         Ref('StarSegment'), code_only=False
-    ),
-)
+    )
+
+    def iter_raw_references(self):
+        """Generate a list of reference strings and elements.
+
+        Each element is a tuple of (str, segment). If some are
+        split, then a segment may appear twice, but the substring
+        will only appear once.
+        """
+        # Extract the references from those identifiers (because some may be quoted)
+        for elem in self.recursive_crawl(('identifier', 'star')):
+            yield from self._iter_reference_parts(elem)
+
+
+@ansi_dialect.segment()
+class WildcardExpressionSegment(BaseSegment):
+    """A star (*) expression for a SELECT clause.
+
+    This is seperate from the identifier to allow for
+    some dialects which extend this logic to allow
+    REPLACE, EXCEPT or similar clauses e.g. BigQuery.
+    """
+    type = "wildcard_expression"
+    match_grammar = Sequence(
+        # *, blah.*, blah.blah.*, etc.
+        Ref('WildcardIdentifierSegment')
+    )
 
 
 @ansi_dialect.segment()
@@ -549,7 +602,7 @@ class SelectTargetElementSegment(BaseSegment):
     # Important to split elements before parsing, otherwise debugging is really hard.
     match_grammar = OneOf(
         # *, blah.*, blah.blah.*, etc.
-        Ref('WildcardSelectTargetElementGrammar'),
+        Ref('WildcardExpressionSegment'),
         GreedyUntil(
             'FROM', 'LIMIT',
             Ref('CommaSegment'),
@@ -560,7 +613,7 @@ class SelectTargetElementSegment(BaseSegment):
 
     parse_grammar = OneOf(
         # *, blah.*, blah.blah.*, etc.
-        Ref('WildcardSelectTargetElementGrammar'),
+        Ref('WildcardExpressionSegment'),
         Sequence(
             OneOf(
                 Ref('LiteralGrammar'),
@@ -592,7 +645,7 @@ class SelectClauseSegment(BaseSegment):
     match_grammar = StartsWith(
         Sequence(
             'SELECT',
-            Ref('WildcardSelectTargetElementGrammar', optional=True)
+            Ref('WildcardExpressionSegment', optional=True)
         ),
         terminator=OneOf('FROM', 'LIMIT', Ref('SetOperatorSegment')),
         enforce_whitespace_preceeding_terminator=True
diff --git a/src/sqlfluff/dialects/dialect_bigquery.py b/src/sqlfluff/dialects/dialect_bigquery.py
index efe28499e..4538d370c 100644
--- a/src/sqlfluff/dialects/dialect_bigquery.py
+++ b/src/sqlfluff/dialects/dialect_bigquery.py
@@ -6,7 +6,7 @@ and
 https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#string_and_bytes_literals
 """
 
-from ..parser import (BaseSegment, NamedSegment, OneOf, Ref, Sequence, Bracketed, Delimited, AnyNumberOf)
+from ..parser import (BaseSegment, NamedSegment, OneOf, Ref, Sequence, Bracketed, Delimited)
 
 from .dialect_ansi import ansi_dialect
 
@@ -80,25 +80,22 @@ bigquery_dialect.replace(
             Ref('SingleIdentifierGrammar'),
             optional=True
         )
-    ),
-    WildcardSelectTargetElementGrammar=Sequence(
+    )
+)
+
+
+@bigquery_dialect.segment(replace=True)
+class WildcardExpressionSegment(BaseSegment):
+    """An extension of the star expression for Bigquery."""
+    type = 'wildcard_expression'
+    match_grammar = Sequence(
         # *, blah.*, blah.blah.*, etc.
-        Sequence(
-            AnyNumberOf(
-                Sequence(
-                    Ref('SingleIdentifierGrammar'),
-                    Ref('DotSegment'),
-                    code_only=True
-                )
-            ),
-            Ref('StarSegment'), code_only=False
-        ),
+        Ref('WildcardIdentifierSegment'),
         # Optional EXCEPT or REPLACE clause
         # https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#select_replace
         Ref('ExceptClauseSegment', optional=True),
         Ref('ReplaceClauseSegment', optional=True)
-    ),
-)
+    )
 
 
 @bigquery_dialect.segment()
diff --git a/src/sqlfluff/parser/segments_base.py b/src/sqlfluff/parser/segments_base.py
index a1c3f702e..128d35f5d 100644
--- a/src/sqlfluff/parser/segments_base.py
+++ b/src/sqlfluff/parser/segments_base.py
@@ -949,9 +949,16 @@ class BaseSegment:
         return buff
 
     def recursive_crawl(self, seg_type):
-        """Recursively crawl for segments of a given type."""
+        """Recursively crawl for segments of a given type.
+
+        Args:
+            seg_type: :obj:`str` or :obj:`tuple` of :obj:`str`: which specifies
+                the type of elements to look for.
+        """
         # Check this segment
-        if self.type == seg_type:
+        if isinstance(seg_type, str) and self.type == seg_type:
+            yield self
+        elif isinstance(seg_type, tuple) and self.type in seg_type:
             yield self
         # Recurse
         for seg in self.segments:
diff --git a/src/sqlfluff/rules/std.py b/src/sqlfluff/rules/std.py
index 2e4ae74a8..b71af1c83 100644
--- a/src/sqlfluff/rules/std.py
+++ b/src/sqlfluff/rules/std.py
@@ -1311,7 +1311,7 @@ class Rule_L013(BaseCrawler):
         if segment.type == 'select_target_element':
             if not any(e.type == 'alias_expression' for e in segment.segments):
                 types = {e.type for e in segment.segments if e.name != 'star'}
-                unallowed_types = types - {'whitespace', 'newline', 'object_reference'}
+                unallowed_types = types - {'whitespace', 'newline', 'object_reference', 'wildcard_expression'}
                 if len(unallowed_types) > 0:
                     # No fixes, because we don't know what the alias should be,
                     # the user should document it themselves.
@@ -2164,6 +2164,8 @@ class Rule_L020(BaseCrawler):
             # potential others.
             sc = segment.get_child('select_clause')
             reference_buffer = list(sc.recursive_crawl('object_reference'))
+            # Add any wildcard references
+            reference_buffer += list(sc.recursive_crawl('wildcard_identifier'))
             for potential_clause in ('where_clause', 'groupby_clause', 'having_clause', 'orderby_clause'):
                 clause = segment.get_child(potential_clause)
                 if clause:
@@ -2463,22 +2465,6 @@ class Rule_L025(Rule_L020):
 
     """
 
-    @staticmethod
-    def _extract_type_tbl_reference(reference):
-        """Extract the type of reference and the referenced alias.
-
-        Returns:
-            :obj:`tuple` of (alias_type, alias)
-
-        """
-        this_ref_type = 'qualified' if reference.is_qualified() else 'unqualified'
-        ref_elems = list(reference.iter_raw_references())
-        if len(ref_elems) >= 2:
-            tbl_ref = ref_elems[-2]
-        else:
-            tbl_ref = None
-        return this_ref_type, tbl_ref
-
     def _lint_references_and_aliases(self, aliases, references, col_aliases, using_cols, parent_select):
         """Check all aliased references against tables referenced in the query."""
         # A buffer to keep any violations.
@@ -2486,7 +2472,7 @@ class Rule_L025(Rule_L020):
         # Check all the references that we have, keep track of which aliases we refer to.
         tbl_refs = set()
         for r in references:
-            _, tbl_ref = self._extract_type_tbl_reference(r)
+            tbl_ref = r.extract_reference(level=2)
             if tbl_ref:
                 tbl_refs.add(tbl_ref[0])
 
@@ -2531,7 +2517,7 @@ class Rule_L026(Rule_L025):
 
         # Check all the references that we have, do they reference present aliases?
         for r in references:
-            _, tbl_ref = self._extract_type_tbl_reference(r)
+            tbl_ref = r.extract_reference(level=2)
             # Check whether the string in the list of strings
             if tbl_ref and tbl_ref[0] not in [a[0] for a in aliases]:
                 # Last check, this *might* be a correlated subquery reference.
@@ -2584,7 +2570,7 @@ class Rule_L027(Rule_L025):
         violation_buff = []
         # Check all the references that we have.
         for r in references:
-            this_ref_type, _ = self._extract_type_tbl_reference(r)
+            this_ref_type = r.qualification()
             if (
                 this_ref_type == 'unqualified'
                 and r.raw not in col_aliases
@@ -2656,7 +2642,10 @@ class Rule_L028(Rule_L025):
         # Check all the references that we have.
         seen_ref_types = set()
         for r in references:
-            this_ref_type, _ = self._extract_type_tbl_reference(r)
+            # We skip any unqualified wildcard references (i.e. *). They shouldn't count.
+            if not r.is_qualified() and r.type == 'wildcard_reference':
+                continue
+            this_ref_type = r.qualification()
             if self.single_table_references == 'consistent':
                 if seen_ref_types and this_ref_type not in seen_ref_types:
                     violation_buff.append(


Test patch:
diff --git a/test/fixtures/parser/ansi/select_simple_b.yml b/test/fixtures/parser/ansi/select_simple_b.yml
index 3335e1644..1342270ef 100644
--- a/test/fixtures/parser/ansi/select_simple_b.yml
+++ b/test/fixtures/parser/ansi/select_simple_b.yml
@@ -5,7 +5,9 @@ file:
       select_clause:
         keyword: select
         select_target_element:
-          keyword: '*'
+          wildcard_expression:
+            wildcard_identifier:
+              star: '*'
       from_clause:
         keyword: from
         table_expression:
diff --git a/test/fixtures/parser/bigquery/select_replace_2.yml b/test/fixtures/parser/bigquery/select_replace_2.yml
index e61f60345..dc800f7cc 100644
--- a/test/fixtures/parser/bigquery/select_replace_2.yml
+++ b/test/fixtures/parser/bigquery/select_replace_2.yml
@@ -4,24 +4,26 @@ file:
       select_clause:
         keyword: SELECT
         select_target_element:
-          keyword: '*'
-          select_replace_clause:
-            keyword: REPLACE
-            start_bracket: (
-            select_target_element:
-              function:
-                function_name: CAST
-                start_bracket: (
-                expression:
-                  literal: '1'
-                keyword: AS
-                data_type:
-                  data_type_identifier: BOOLEAN
-                end_bracket: )
-              alias_expression:
-                keyword: AS
-                identifier: foo
-            end_bracket: )
+          wildcard_expression:
+            wildcard_identifier:
+              star: '*'
+            select_replace_clause:
+              keyword: REPLACE
+              start_bracket: (
+              select_target_element:
+                function:
+                  function_name: CAST
+                  start_bracket: (
+                  expression:
+                    literal: '1'
+                  keyword: AS
+                  data_type:
+                    data_type_identifier: BOOLEAN
+                  end_bracket: )
+                alias_expression:
+                  keyword: AS
+                  identifier: foo
+              end_bracket: )
       from_clause:
         keyword: FROM
         table_expression:
diff --git a/test/fixtures/parser/snowflake/snowflake_pivot.yml b/test/fixtures/parser/snowflake/snowflake_pivot.yml
index 95bc87585..b448fae6a 100644
--- a/test/fixtures/parser/snowflake/snowflake_pivot.yml
+++ b/test/fixtures/parser/snowflake/snowflake_pivot.yml
@@ -4,7 +4,9 @@ file:
       select_clause:
         keyword: SELECT
         select_target_element:
-          keyword: '*'
+          wildcard_expression:
+            wildcard_identifier:
+              star: '*'
       from_clause:
         keyword: FROM
         table_expression:
diff --git a/test/fixtures/templater/jinja_g_macros/jinja.yml b/test/fixtures/templater/jinja_g_macros/jinja.yml
index 8cf65cac9..2eca31495 100644
--- a/test/fixtures/templater/jinja_g_macros/jinja.yml
+++ b/test/fixtures/templater/jinja_g_macros/jinja.yml
@@ -50,7 +50,9 @@ file:
             select_clause:
               keyword: select
               select_target_element:
-                keyword: '*'
+                wildcard_expression:
+                  wildcard_identifier:
+                    star: '*'
             from_clause:
               keyword: from
               table_expression:
diff --git a/test/rules_std_test.py b/test/rules_std_test.py
index 2588ad02b..5f50f1b47 100644
--- a/test/rules_std_test.py
+++ b/test/rules_std_test.py
@@ -24,7 +24,10 @@ def assert_rule_fail_in_sql(code, sql, configs=None):
     print("Parsed:\n {0}".format(parsed.stringify()))
     lerrs, _, _, _ = r.crawl(parsed, dialect=cfg.get('dialect_obj'), fix=True)
     print("Errors Found: {0}".format(lerrs))
-    assert any(v.rule.code == code for v in lerrs)
+    if not any(v.rule.code == code for v in lerrs):
+        pytest.fail(
+            "No {0} failures found in query which should fail.".format(code),
+            pytrace=False)
     fixed = parsed  # use this as our buffer (yes it's a bit of misnomer right here)
     while True:
         # We get the errors again, but this time skip the assertion
@@ -46,7 +49,6 @@ def assert_rule_fail_in_sql(code, sql, configs=None):
             if fixes == l_fixes:
                 raise RuntimeError(
                     "Fixes aren't being applied: {0!r}".format(fixes))
-
     return fixed.raw
 
 
@@ -59,7 +61,10 @@ def assert_rule_pass_in_sql(code, sql, configs=None):
     print("Parsed:\n {0}".format(parsed.stringify()))
     lerrs, _, _, _ = r.crawl(parsed, dialect=cfg.get('dialect_obj'), fix=True)
     print("Errors Found: {0}".format(lerrs))
-    assert not any(v.rule.code == code for v in lerrs)
+    if any(v.rule.code == code for v in lerrs):
+        pytest.fail(
+            "Found {0} failures in query which should pass.".format(code),
+            pytrace=False)
 
 
 # ############## STD RULES TESTS
@@ -193,7 +198,10 @@ def assert_rule_pass_in_sql(code, sql, configs=None):
     ('L026', 'pass', 'SELECT * FROM db.sc.tbl2\nWHERE a NOT IN (SELECT tbl2.a FROM db.sc.tbl1)\n', None, None),  # Correlated subquery.
     # Make sure comments are aligned properly
     ('L003', 'pass', 'SELECT *\nFROM\n    t1\n-- Comment\nJOIN t2 USING (user_id)', None, None),
-    ('L003', 'fail', 'SELECT *\nFROM\n    t1\n    -- Comment\nJOIN t2 USING (user_id)', 'SELECT *\nFROM\n    t1\n-- Comment\nJOIN t2 USING (user_id)', None)
+    ('L003', 'fail', 'SELECT *\nFROM\n    t1\n    -- Comment\nJOIN t2 USING (user_id)', 'SELECT *\nFROM\n    t1\n-- Comment\nJOIN t2 USING (user_id)', None),
+    # L013 & L025 Fixes with https://github.com/sqlfluff/sqlfluff/issues/449
+    ('L013', 'pass', 'select ps.*, pandgs.blah from ps join pandgs using(moo)', None, None),
+    ('L025', 'pass', 'select ps.*, pandgs.blah from ps join pandgs using(moo)', None, None)
 ])
 def test__rules__std_string(rule, pass_fail, qry, fixed, configs):
     """Test that a rule passes/fails on a given string.