Repository: sqlfluff/sqlfluff
Pull request: Add column index identifier in snowflake dialect
Resolves https://github.com/sqlfluff/sqlfluff/issues/451

Problem statement:
'Unable to lex characters: ''$1 as' in snowflake
```
with sweets as (
    select $1 as type, $2 as price
    from (values ('toffee', 5), ('starburst', 8), ('flying_saucer', 1))
)

select * from sweets

```

Results in
```
== [sweets.sql] FAIL
L:   2 | P:  12 |  LXR | Unable to lex characters: ''$1 as type'...'
L:   2 | P:  12 |  PRS | Found unparsable section: '$1 as type'
L:   2 | P:  12 | L013 | Column expression without alias. Use explicit `AS`
                       | clause.
L:   2 | P:  24 |  LXR | Unable to lex characters: ''$2 as pric'...'
L:   2 | P:  24 |  PRS | Found unparsable section: '$2 as price'
L:   2 | P:  24 | L013 | Column expression without alias. Use explicit `AS`
                       | clause.
```

Hints from issue discussion:
I know this is valid snowflake syntax for referring to columns by their index, but I can't find a reference to this syntax in the mysql or the postgres docs.

@barrywhart - do you know if BigQuery supports a similar syntax?
I'm no expert, but I haven't seen anything like that.

I did see this code from someone, which seems to achieve a similar outcome with different syntax. (This syntax confused me, and we ended up reworking it for unrelated reasons.) If you'd like me to research this area further, please let me know.

```
SELECT
  'low' AS a,
  'medium' AS b,
  `{{gcp_project}}.{{dataset}}.compareConfidenceLabels`('low', 'high') AS result
FROM UNNEST([1])
```


@NiallRees want to have a go at this one? Assuming it's just a snowflake feature we should only need to add it to the snowflake dialect.

It should just involve adding the `$123` syntax to the lexer, probably as a regex and using `snowflake_dialect.insert_lexer_struct()`, and then adding an appropriate segment to the dialect. I think it should be parsed as a kind of identifier rather than a type of literal, that way all of the linting rules will apply to it nicely.

I'd probably start by extending the `SingleIdentifierGrammar` in snowflake to also include this use case, but I'm not 100% that that is the most elegant place to add this in 🤔 .

Commit message: Add column index identifier in snowflake dialect and lexer

Patch to review:
diff --git a/src/sqlfluff/dialects/dialect_snowflake.py b/src/sqlfluff/dialects/dialect_snowflake.py
index 1d84ebee1b9..9427d554f62 100644
--- a/src/sqlfluff/dialects/dialect_snowflake.py
+++ b/src/sqlfluff/dialects/dialect_snowflake.py
@@ -26,6 +26,13 @@
     before='not_equal'
 )
 
+snowflake_dialect.insert_lexer_struct(
+    # Column selector
+    # https://docs.snowflake.com/en/sql-reference/sql/select.html#parameters
+    [("column_selector", "regex", r"\$[0-9]+", dict(is_code=True))],
+    before='not_equal'
+)
+
 snowflake_dialect.sets('unreserved_keywords').update([
     'LATERAL',
     'BERNOULLI',
@@ -53,6 +60,9 @@
     QuotedSemiStructuredElementSegment=NamedSegment.make(
         'double_quote', name='quoted_semi_structured_element',
         type='semi_structured_element'),
+    ColumnIndexIdentifierSegment=ReSegment.make(
+        r"\$[0-9]+", name='column_index_identifier_segment',
+        type='column_index_identifier'),
 )
 
 snowflake_dialect.replace(
@@ -77,7 +87,12 @@
         ),
         Ref('SamplingExpressionSegment', optional=True),
         Ref('TableAliasExpressionSegment', optional=True)
-    )
+    ),
+    SingleIdentifierGrammar=OneOf(
+        Ref('NakedIdentifierSegment'),
+        Ref('QuotedIdentifierSegment'),
+        Ref('ColumnIndexIdentifierSegment')
+    ),
 )
 
 
diff --git a/src/sqlfluff/parser/lexer.py b/src/sqlfluff/parser/lexer.py
index ebbcb859a3a..1a1cf47c3f8 100644
--- a/src/sqlfluff/parser/lexer.py
+++ b/src/sqlfluff/parser/lexer.py
@@ -198,7 +198,7 @@ class RegexMatcher(SingletonMatcher):
     """This RegexMatcher matches based on regular expressions."""
 
     def __init__(self, *args, **kwargs):
-        super(RegexMatcher, self).__init__(*args, **kwargs)
+        super().__init__(*args, **kwargs)
         # We might want to configure this at some point, but for now, newlines
         # do get matched by .
         flags = re.DOTALL
diff --git a/test/fixtures/parser/snowflake/snowflake_col_position.sql b/test/fixtures/parser/snowflake/snowflake_col_position.sql
new file mode 100644
index 00000000000..9cb66cb8d94
--- /dev/null
+++ b/test/fixtures/parser/snowflake/snowflake_col_position.sql
@@ -0,0 +1,4 @@
+-- In snowflake the column position is denoted with $n syntax (e.g. $1, $2)
+-- https://docs.snowflake.com/en/sql-reference/sql/select.html#parameters
+select $1 as type, $2 as price
+from (values ('toffee', 5), ('starburst', 8), ('flying_saucer', 1))
\ No newline at end of file


Code review comments:
diff_hunk: @@ -53,6 +60,9 @@
     QuotedSemiStructuredElementSegment=NamedSegment.make(
         'double_quote', name='quoted_semi_structured_element',
         type='semi_structured_element'),
+    ColumnIndexIdentifierSegment=ReSegment.make(
+        r"\$[0-9]+", name='column_index_identifier_segment',
+        type='column_index_identifier'),
path: src/sqlfluff/dialects/dialect_snowflake.py
text: @user1:
In this case that's an excellent question. Both `type` and `name` can be referred to downstream by linting rules when linting. In this case I think this should be of `type` `identifier` because I think several rules should also apply to these. It's possible that it might cause issues but I'd like to at least try that. The `name` you've chosen looks sensible.