Repository: sqlfluff/sqlfluff

Issue:
Parsing error when using Snowflake single or double-colon syntax for semi-structured data traversal and casting
When linting queries like the following using Snowflake's double-colon casting syntax:

`SELECT 
  ID :: VARCHAR as id, 
  OBJ : userId :: VARCHAR as user_id, 
  OBJ : offerId :: VARCHAR as offer_id, 
  redemptions.value : awardedReceiptId :: VARCHAR as awarded_receipt_id, `


There is the following error: `L:   7 | P:  20 |  PRS | Found unparsable section: ' : awardedReceiptId ::
                       | VARCHAR as awarde...'`

https://docs.snowflake.com/en/sql-reference/functions/cast.html
https://docs.snowflake.com/en/user-guide/querying-semistructured.html#traversing-semi-structured-data



Discussion:
emekdahl: Thank you!!

Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 3bca3a228..cf7f06bbd 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -58,6 +58,7 @@ and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0
 
 ### Changed
 
+- [#634](https://github.com/sqlfluff/sqlfluff/issues/635) Fixed parsing of semi-structured objects in the snowflake of dialects with whitespace gaps.
 - [#632](https://github.com/sqlfluff/sqlfluff/pull/632) Handle internal errors elegantly, reporting the stacktrace and the error-surfacing file.
 - [#633](https://github.com/sqlfluff/sqlfluff/issues/633) Improve message for when an automatic fix is not available for L004.
 - Big refactor of logging internally. `Linter` is now decoupled from
diff --git a/src/sqlfluff/core/dialects/dialect_snowflake.py b/src/sqlfluff/core/dialects/dialect_snowflake.py
index 6ebecd6c2..4387ff61e 100644
--- a/src/sqlfluff/core/dialects/dialect_snowflake.py
+++ b/src/sqlfluff/core/dialects/dialect_snowflake.py
@@ -289,14 +289,11 @@ class SemiStructuredAccessorSegment(BaseSegment):
                     Ref("QuotedSemiStructuredElementSegment"),
                 ),
                 Ref("ArrayAccessorSegment", optional=True),
-                # No extra whitespace
-                allow_gaps=False,
+                allow_gaps=True,
             ),
-            # No extra whitespace
-            allow_gaps=False,
+            allow_gaps=True,
         ),
-        # No extra whitespace
-        allow_gaps=False,
+        allow_gaps=True,
     )
 
 


Test patch:
diff --git a/test/core/dialects/snowflake_test.py b/test/core/dialects/snowflake_test.py
index ccc9d2302..c680e301b 100644
--- a/test/core/dialects/snowflake_test.py
+++ b/test/core/dialects/snowflake_test.py
@@ -3,7 +3,10 @@
 import pytest
 
 from sqlfluff.core import Linter
-from sqlfluff.core.dialects.dialect_snowflake import UseStatementSegment
+from sqlfluff.core.dialects.dialect_snowflake import (
+    UseStatementSegment,
+    SemiStructuredAccessorSegment,
+)
 
 
 @pytest.mark.parametrize(
@@ -16,6 +19,11 @@ from sqlfluff.core.dialects.dialect_snowflake import UseStatementSegment
         (UseStatementSegment, 'USE SCHEMA "MY_DATABASE"."MY_SCHEMA";'),
         (UseStatementSegment, 'USE SCHEMA "MY_SCHEMA";'),
         (UseStatementSegment, 'USE "MY_DATABASE"."MY_SCHEMA";'),
+        # Testing https://github.com/sqlfluff/sqlfluff/issues/634
+        (
+            SemiStructuredAccessorSegment,
+            "SELECT ID :: VARCHAR as id, OBJ : userId :: VARCHAR as user_id from x",
+        ),
     ],
 )
 def test_snowflake_queries(segment_cls, raw, caplog):