Repository: sqlfluff/sqlfluff
Pull request: ANSI: Add TABLESAMPLE support
### Brief summary of the change made
closes #1809 

The SQL Standard 2003 introduced the TABLESAMPLE clause, which has been available in postgres since  version 9.5. I personally use it on AWS Athena/PrestoDB to reduce the size of the result set.

The syntax, as seen [here](https://wiki.postgresql.org/wiki/TABLESAMPLE_Implementation#Syntax) looks like:

```ebnf
<table reference> ::= <table factor> | <joined table>
<table factor> ::= <table primary> [ <sample clause> ]
<table primary> ::= <table or query name> [ [ AS ] <correlation name> ]
<sample clause> ::= TABLESAMPLE <sample method> <left paren> 
                    <sample percentage> <right paren> [ <repeatable clause> ]
<sample method> ::= BERNOULLI | SYSTEM
<repeatable clause> ::= REPEATABLE <left paren> <repeat argument> <right paren>
<sample percentage> ::= <numeric value expression>
<repeat argument> ::= <numeric value expression>
```

And it should be noted that `tablesample` is a totally fine alias for a table name.

### Proposed enhancements
As noted in the previous link, the `sample percentage` should not be `< 0` nor `> 100`. I tried to create a new literal for numeric values in that range but... it didn't go so well. Which got me thinking (and I'd like to hear some input on this before moving forward with it) if instead there was any validation mechanisms I could leverage, but I found none at a quick glance nor in the contribution tutorial.

### Are there any other side effects of this change that we should be aware of?
`isort` might have happened here and there.

Problem statement:
TABLESAMPLE BERNOULLI fails as unparsable section
`TABLESAMPLE BERNOULLI` statements fail.

## Expected Behaviour
`TABLESAMPLE` statements should not fail, at least using the `postgres` dialect (though I believe even ansi should support this). 

## Observed Behaviour
The following query fails linting:

```sql
SELECT colname
FROM tablename TABLESAMPLE BERNOULLI (1)
```

with message:
```bash
== [ex.sql] FAIL
L:   2 | P:  16 |  PRS | Line 2, Position 16: Found unparsable section:
                       | 'TABLESAMPLE BERNOULLI (1)'
All Finished 📜 🎉!
```

## Steps to Reproduce
1. Create a file called `ex.sql` with the previously indicated query
2. Run `sqlfluff lint ex.sql --dialect postgres`

## Dialect
`postgres`

## Version
Python 3.9.6
sqlfluff 0.7.1

## Configuration
`None`

Hints from issue discussion:
This is a great first issue that should be easy to add support for, following the steps here: https://github.com/sqlfluff/sqlfluff/wiki/Contributing-Dialect-Changes

Commit message: run black

Patch to review:
diff --git a/src/sqlfluff/dialects/dialect_ansi.py b/src/sqlfluff/dialects/dialect_ansi.py
index 0694f858850..30cc7acdae1 100644
--- a/src/sqlfluff/dialects/dialect_ansi.py
+++ b/src/sqlfluff/dialects/dialect_ansi.py
@@ -13,44 +13,42 @@
 """
 
 from enum import Enum
-from typing import Generator, List, Tuple, NamedTuple, Optional, Union
+from typing import Generator, List, NamedTuple, Optional, Tuple, Union
 
+from sqlfluff.core.dialects.base import Dialect
+from sqlfluff.core.dialects.common import AliasInfo
 from sqlfluff.core.parser import (
-    Matchable,
-    BaseSegment,
-    BaseFileSegment,
-    KeywordSegment,
-    SymbolSegment,
-    Sequence,
-    GreedyUntil,
-    StartsWith,
-    OneOf,
-    Delimited,
-    Bracketed,
     AnyNumberOf,
-    Ref,
-    SegmentGenerator,
     Anything,
-    Indent,
+    BaseFileSegment,
+    BaseSegment,
+    Bracketed,
+    CodeSegment,
+    CommentSegment,
+    Conditional,
     Dedent,
+    Delimited,
+    GreedyUntil,
+    Indent,
+    KeywordSegment,
+    Matchable,
+    NamedParser,
+    NewlineSegment,
     Nothing,
+    OneOf,
     OptionallyBracketed,
-    StringLexer,
+    Ref,
     RegexLexer,
-    CodeSegment,
-    CommentSegment,
-    WhitespaceSegment,
-    NewlineSegment,
-    StringParser,
-    NamedParser,
     RegexParser,
-    Conditional,
+    SegmentGenerator,
+    Sequence,
+    StartsWith,
+    StringLexer,
+    StringParser,
+    SymbolSegment,
+    WhitespaceSegment,
 )
-
-from sqlfluff.core.dialects.base import Dialect
-from sqlfluff.core.dialects.common import AliasInfo
 from sqlfluff.core.parser.segments.base import BracketedSegment
-
 from sqlfluff.dialects.dialect_ansi_keywords import (
     ansi_reserved_keywords,
     ansi_unreserved_keywords,
@@ -1062,7 +1060,12 @@ class FromExpressionElementSegment(BaseSegment):
         OptionallyBracketed(Ref("TableExpressionSegment")),
         # https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays#flattening_arrays
         Sequence("WITH", "OFFSET", optional=True),
-        Ref("AliasExpressionSegment", optional=True),
+        OneOf(
+            Sequence(Ref("AliasExpressionSegment"), Ref("SamplingExpressionSegment")),
+            Ref("SamplingExpressionSegment"),
+            Ref("AliasExpressionSegment"),
+            optional=True,
+        ),
         Ref("PostTableExpressionGrammar", optional=True),
     )
 
@@ -3157,3 +3160,20 @@ class DatePartFunctionNameSegment(BaseSegment):
 
     type = "function_name"
     match_grammar = Sequence("DATEADD")
+
+
+@ansi_dialect.segment()
+class SamplingExpressionSegment(BaseSegment):
+    """A sampling expression."""
+
+    type = "ansi_sample_expression"
+    match_grammar = Sequence(
+        "TABLESAMPLE",
+        OneOf("BERNOULLI", "SYSTEM"),
+        Bracketed(Ref("NumericLiteralSegment")),
+        Sequence(
+            OneOf("REPEATABLE"),
+            Bracketed(Ref("NumericLiteralSegment")),
+            optional=True,
+        ),
+    )
diff --git a/src/sqlfluff/dialects/dialect_snowflake.py b/src/sqlfluff/dialects/dialect_snowflake.py
index c67a6fd9cef..e16c2ec44ca 100644
--- a/src/sqlfluff/dialects/dialect_snowflake.py
+++ b/src/sqlfluff/dialects/dialect_snowflake.py
@@ -7,28 +7,27 @@
 
 from sqlfluff.core.dialects import load_raw_dialect
 from sqlfluff.core.parser import (
-    BaseSegment,
-    OneOf,
-    Ref,
-    Sequence,
     AnyNumberOf,
-    SymbolSegment,
-    Bracketed,
     Anything,
+    BaseSegment,
+    Bracketed,
+    CodeSegment,
+    Dedent,
     Delimited,
-    StartsWith,
     Indent,
-    Dedent,
-    RegexLexer,
-    StringLexer,
-    CodeSegment,
-    StringParser,
     NamedParser,
+    OneOf,
+    Ref,
+    RegexLexer,
     RegexParser,
     SegmentGenerator,
+    Sequence,
+    StartsWith,
+    StringLexer,
+    StringParser,
+    SymbolSegment,
 )
 from sqlfluff.core.parser.grammar.anyof import OptionallyBracketed
-
 from sqlfluff.dialects.dialect_snowflake_keywords import (
     snowflake_reserved_keywords,
     snowflake_unreserved_keywords,
@@ -595,7 +594,7 @@ class FromUnpivotExpressionSegment(BaseSegment):
     )
 
 
-@snowflake_dialect.segment()
+@snowflake_dialect.segment(replace=True)
 class SamplingExpressionSegment(BaseSegment):
     """A sampling expression."""
 
diff --git a/test/fixtures/dialects/ansi/select_sample_bernoulli_10.sql b/test/fixtures/dialects/ansi/select_sample_bernoulli_10.sql
new file mode 100644
index 00000000000..fd85031ed87
--- /dev/null
+++ b/test/fixtures/dialects/ansi/select_sample_bernoulli_10.sql
@@ -0,0 +1,2 @@
+SELECT *
+FROM foo TABLESAMPLE BERNOULLI (10)
diff --git a/test/fixtures/dialects/ansi/select_sample_bernoulli_10.yml b/test/fixtures/dialects/ansi/select_sample_bernoulli_10.yml
new file mode 100644
index 00000000000..e6f5811d569
--- /dev/null
+++ b/test/fixtures/dialects/ansi/select_sample_bernoulli_10.yml
@@ -0,0 +1,29 @@
+# YML test files are auto-generated from SQL files and should not be edited by
+# hand. To help enforce this, the "hash" field in the file must match a hash
+# computed by SQLFluff when running the tests. Please run
+# `python test/generate_parse_fixture_yml.py`  to generate them after adding or
+# altering SQL files.
+_hash: 1df4ed025e6c3f7b9d8035f3cb627e366ad3ad18d0154918f8a858bd5b07acdb
+file:
+  statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          wildcard_expression:
+            wildcard_identifier:
+              star: '*'
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                identifier: foo
+            ansi_sample_expression:
+            - keyword: TABLESAMPLE
+            - keyword: BERNOULLI
+            - bracketed:
+                start_bracket: (
+                literal: '10'
+                end_bracket: )
diff --git a/test/fixtures/dialects/ansi/select_sample_bernoulli_10_aliased.sql b/test/fixtures/dialects/ansi/select_sample_bernoulli_10_aliased.sql
new file mode 100644
index 00000000000..afda8011a08
--- /dev/null
+++ b/test/fixtures/dialects/ansi/select_sample_bernoulli_10_aliased.sql
@@ -0,0 +1,2 @@
+SELECT f.colname
+FROM foo AS f TABLESAMPLE BERNOULLI (10)
diff --git a/test/fixtures/dialects/ansi/select_sample_bernoulli_10_aliased.yml b/test/fixtures/dialects/ansi/select_sample_bernoulli_10_aliased.yml
new file mode 100644
index 00000000000..812a2921e4a
--- /dev/null
+++ b/test/fixtures/dialects/ansi/select_sample_bernoulli_10_aliased.yml
@@ -0,0 +1,33 @@
+# YML test files are auto-generated from SQL files and should not be edited by
+# hand. To help enforce this, the "hash" field in the file must match a hash
+# computed by SQLFluff when running the tests. Please run
+# `python test/generate_parse_fixture_yml.py`  to generate them after adding or
+# altering SQL files.
+_hash: 0c205ea67995e31c393fcef49eceea904abca0f6cbc24ebe55c1af34aad36589
+file:
+  statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          column_reference:
+          - identifier: f
+          - dot: .
+          - identifier: colname
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                identifier: foo
+            alias_expression:
+              keyword: AS
+              identifier: f
+            ansi_sample_expression:
+            - keyword: TABLESAMPLE
+            - keyword: BERNOULLI
+            - bracketed:
+                start_bracket: (
+                literal: '10'
+                end_bracket: )
diff --git a/test/fixtures/dialects/ansi/select_sample_bernoulli_10_repeatable.sql b/test/fixtures/dialects/ansi/select_sample_bernoulli_10_repeatable.sql
new file mode 100644
index 00000000000..22a3e9532b5
--- /dev/null
+++ b/test/fixtures/dialects/ansi/select_sample_bernoulli_10_repeatable.sql
@@ -0,0 +1,2 @@
+SELECT *
+FROM foo TABLESAMPLE BERNOULLI (10) REPEATABLE (100)
diff --git a/test/fixtures/dialects/ansi/select_sample_bernoulli_10_repeatable.yml b/test/fixtures/dialects/ansi/select_sample_bernoulli_10_repeatable.yml
new file mode 100644
index 00000000000..77db4517cd9
--- /dev/null
+++ b/test/fixtures/dialects/ansi/select_sample_bernoulli_10_repeatable.yml
@@ -0,0 +1,34 @@
+# YML test files are auto-generated from SQL files and should not be edited by
+# hand. To help enforce this, the "hash" field in the file must match a hash
+# computed by SQLFluff when running the tests. Please run
+# `python test/generate_parse_fixture_yml.py`  to generate them after adding or
+# altering SQL files.
+_hash: da814607b68ee833295ef946feb123a28bf5926d35a1cd5fc61c2cb9001b8d05
+file:
+  statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          wildcard_expression:
+            wildcard_identifier:
+              star: '*'
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                identifier: foo
+            ansi_sample_expression:
+            - keyword: TABLESAMPLE
+            - keyword: BERNOULLI
+            - bracketed:
+                start_bracket: (
+                literal: '10'
+                end_bracket: )
+            - keyword: REPEATABLE
+            - bracketed:
+                start_bracket: (
+                literal: '100'
+                end_bracket: )
diff --git a/test/fixtures/dialects/ansi/select_sample_bernoulli_order_limit.sql b/test/fixtures/dialects/ansi/select_sample_bernoulli_order_limit.sql
new file mode 100644
index 00000000000..edeb0d2b174
--- /dev/null
+++ b/test/fixtures/dialects/ansi/select_sample_bernoulli_order_limit.sql
@@ -0,0 +1,4 @@
+SELECT col1, col2
+FROM foo TABLESAMPLE BERNOULLI (10)
+ORDER BY col1
+LIMIT 100
diff --git a/test/fixtures/dialects/ansi/select_sample_bernoulli_order_limit.yml b/test/fixtures/dialects/ansi/select_sample_bernoulli_order_limit.yml
new file mode 100644
index 00000000000..9e7b44fc252
--- /dev/null
+++ b/test/fixtures/dialects/ansi/select_sample_bernoulli_order_limit.yml
@@ -0,0 +1,40 @@
+# YML test files are auto-generated from SQL files and should not be edited by
+# hand. To help enforce this, the "hash" field in the file must match a hash
+# computed by SQLFluff when running the tests. Please run
+# `python test/generate_parse_fixture_yml.py`  to generate them after adding or
+# altering SQL files.
+_hash: 6e079360fe1d49fd94dbec1972d58bebd075a855999694a178564e7e3c8ce098
+file:
+  statement:
+    select_statement:
+      select_clause:
+      - keyword: SELECT
+      - select_clause_element:
+          column_reference:
+            identifier: col1
+      - comma: ','
+      - select_clause_element:
+          column_reference:
+            identifier: col2
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                identifier: foo
+            ansi_sample_expression:
+            - keyword: TABLESAMPLE
+            - keyword: BERNOULLI
+            - bracketed:
+                start_bracket: (
+                literal: '10'
+                end_bracket: )
+      orderby_clause:
+      - keyword: ORDER
+      - keyword: BY
+      - column_reference:
+          identifier: col1
+      limit_clause:
+        keyword: LIMIT
+        literal: '100'
diff --git a/test/fixtures/dialects/ansi/select_sample_system_10.sql b/test/fixtures/dialects/ansi/select_sample_system_10.sql
new file mode 100644
index 00000000000..d3bf761f5e0
--- /dev/null
+++ b/test/fixtures/dialects/ansi/select_sample_system_10.sql
@@ -0,0 +1,2 @@
+SELECT * 
+FROM foo TABLESAMPLE SYSTEM (10)
diff --git a/test/fixtures/dialects/ansi/select_sample_system_10.yml b/test/fixtures/dialects/ansi/select_sample_system_10.yml
new file mode 100644
index 00000000000..8801cfbb72f
--- /dev/null
+++ b/test/fixtures/dialects/ansi/select_sample_system_10.yml
@@ -0,0 +1,29 @@
+# YML test files are auto-generated from SQL files and should not be edited by
+# hand. To help enforce this, the "hash" field in the file must match a hash
+# computed by SQLFluff when running the tests. Please run
+# `python test/generate_parse_fixture_yml.py`  to generate them after adding or
+# altering SQL files.
+_hash: f4d473252e5309535d85784e546453cbc506c71ad05e718ed39cf44cbf62aeee
+file:
+  statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          wildcard_expression:
+            wildcard_identifier:
+              star: '*'
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                identifier: foo
+            ansi_sample_expression:
+            - keyword: TABLESAMPLE
+            - keyword: SYSTEM
+            - bracketed:
+                start_bracket: (
+                literal: '10'
+                end_bracket: )
diff --git a/test/fixtures/dialects/ansi/select_sample_system_10_aliased.sql b/test/fixtures/dialects/ansi/select_sample_system_10_aliased.sql
new file mode 100644
index 00000000000..c44bb6b3715
--- /dev/null
+++ b/test/fixtures/dialects/ansi/select_sample_system_10_aliased.sql
@@ -0,0 +1,2 @@
+SELECT f.colname
+FROM foo AS f TABLESAMPLE SYSTEM (10)
diff --git a/test/fixtures/dialects/ansi/select_sample_system_10_aliased.yml b/test/fixtures/dialects/ansi/select_sample_system_10_aliased.yml
new file mode 100644
index 00000000000..6bc2e45a3d9
--- /dev/null
+++ b/test/fixtures/dialects/ansi/select_sample_system_10_aliased.yml
@@ -0,0 +1,33 @@
+# YML test files are auto-generated from SQL files and should not be edited by
+# hand. To help enforce this, the "hash" field in the file must match a hash
+# computed by SQLFluff when running the tests. Please run
+# `python test/generate_parse_fixture_yml.py`  to generate them after adding or
+# altering SQL files.
+_hash: 9d8fef820122780a9dda393fb1bd81c52840e4e03bd2a447a8edcd2ac9c9f0b8
+file:
+  statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          column_reference:
+          - identifier: f
+          - dot: .
+          - identifier: colname
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                identifier: foo
+            alias_expression:
+              keyword: AS
+              identifier: f
+            ansi_sample_expression:
+            - keyword: TABLESAMPLE
+            - keyword: SYSTEM
+            - bracketed:
+                start_bracket: (
+                literal: '10'
+                end_bracket: )
diff --git a/test/fixtures/dialects/ansi/select_sample_system_10_repeatable.sql b/test/fixtures/dialects/ansi/select_sample_system_10_repeatable.sql
new file mode 100644
index 00000000000..f0e083f0641
--- /dev/null
+++ b/test/fixtures/dialects/ansi/select_sample_system_10_repeatable.sql
@@ -0,0 +1,2 @@
+SELECT * 
+FROM foo TABLESAMPLE SYSTEM (10) REPEATABLE (100)
diff --git a/test/fixtures/dialects/ansi/select_sample_system_10_repeatable.yml b/test/fixtures/dialects/ansi/select_sample_system_10_repeatable.yml
new file mode 100644
index 00000000000..c3d88a4dd3e
--- /dev/null
+++ b/test/fixtures/dialects/ansi/select_sample_system_10_repeatable.yml
@@ -0,0 +1,34 @@
+# YML test files are auto-generated from SQL files and should not be edited by
+# hand. To help enforce this, the "hash" field in the file must match a hash
+# computed by SQLFluff when running the tests. Please run
+# `python test/generate_parse_fixture_yml.py`  to generate them after adding or
+# altering SQL files.
+_hash: 688d81a82fbe9bfa3eced9c9a3e55ad862c990cd6b7d69673b71f2ead7235bb2
+file:
+  statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          wildcard_expression:
+            wildcard_identifier:
+              star: '*'
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                identifier: foo
+            ansi_sample_expression:
+            - keyword: TABLESAMPLE
+            - keyword: SYSTEM
+            - bracketed:
+                start_bracket: (
+                literal: '10'
+                end_bracket: )
+            - keyword: REPEATABLE
+            - bracketed:
+                start_bracket: (
+                literal: '100'
+                end_bracket: )
diff --git a/test/fixtures/dialects/snowflake/snowflake_sample.yml b/test/fixtures/dialects/snowflake/snowflake_sample.yml
index bde9b85d219..d37ae2d7d88 100644
--- a/test/fixtures/dialects/snowflake/snowflake_sample.yml
+++ b/test/fixtures/dialects/snowflake/snowflake_sample.yml
@@ -3,7 +3,7 @@
 # computed by SQLFluff when running the tests. Please run
 # `python test/generate_parse_fixture_yml.py`  to generate them after adding or
 # altering SQL files.
-_hash: f8933c2834abd3b291b874993258fae463bfc7dd7bf9b27cbef197af1ae6fbf1
+_hash: 69ba852540d220d8ee262dc72a4f909f7741c260294bad57bbfa222148ad9459
 file:
   statement:
     select_statement:
@@ -20,9 +20,9 @@ file:
             table_expression:
               table_reference:
                 identifier: real_data
-          snowflake_sample_expression:
-            keyword: sample
-            bracketed:
-              start_bracket: (
-              literal: '20'
-              end_bracket: )
+            snowflake_sample_expression:
+              keyword: sample
+              bracketed:
+                start_bracket: (
+                literal: '20'
+                end_bracket: )


Code review comments:
diff_hunk: @@ -3157,3 +3160,20 @@ class DatePartFunctionNameSegment(BaseSegment):
 
     type = "function_name"
     match_grammar = Sequence("DATEADD")
+
+
+@ansi_dialect.segment()
+class SamplingExpressionSegment(BaseSegment):
+    """A sampling expression."""
+
+    type = "ansi_sample_expression"
path: src/sqlfluff/dialects/dialect_ansi.py
text: @user1:
I think we should just make this:

```suggestion
    type = "sample_expression"
```

@author:
FIxed in [0c4b5579b12060f4de698d3ddc7946248f0216b3](https://github.com/sqlfluff/sqlfluff/pull/1811/commits/0c4b5579b12060f4de698d3ddc7946248f0216b3)