Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Version 23.12.2 fails to parse some COPY clauses #3388

Closed
dangoldin opened this issue May 1, 2024 · 11 comments · Fixed by #3398
Closed

Version 23.12.2 fails to parse some COPY clauses #3388

dangoldin opened this issue May 1, 2024 · 11 comments · Fixed by #3398
Assignees
@VaggelisD

Comments

@dangoldin
Copy link
Contributor

dangoldin commented May 1, 2024 •
edited

Looks as if in prior versions it didn't fully parse and fell back to the default Command but with 23.12.2 it's trying to parse and failing. Maybe we can have it fall back to Command as we work through various options COPY has?

Fully reproducible code snippet

from sqlglot import parse_one

q = """
copy into temp FROM @random_stage/path/
FILE_FORMAT = (
    TYPE=CSV
    FIELD_DELIMITER='|'
    NULL_IF=()
    FIELD_OPTIONALLY_ENCLOSED_BY='"'
    TIMESTAMP_FORMAT='TZHTZM YYYY-MM-DD HH24:MI:SS.FF9'
    DATE_FORMAT='TZHTZM YYYY-MM-DD HH24:MI:SS.FF9'
    BINARY_FORMAT=BASE64
  )
"""

pq = parse_one(q, read="snowflake")
print("Parsed:", pq, "\n")

The above gives the following using 23.12.1:

✗ python test_sqlglot.py 
'copy into temp FROM @random_stage/path/
FILE_FORMAT = (
    TYPE=CSV
    FIELD_DELIMITER='|'
    NUL' contains unsupported syntax. Falling back to parsing as a 'Command'.
Parsed: COPY into temp FROM @random_stage/path/
FILE_FORMAT = (
    TYPE=CSV
    FIELD_DELIMITER='|'
    NULL_IF=()
    FIELD_OPTIONALLY_ENCLOSED_BY='"'
    TIMESTAMP_FORMAT='TZHTZM YYYY-MM-DD HH24:MI:SS.FF9'
    DATE_FORMAT='TZHTZM YYYY-MM-DD HH24:MI:SS.FF9'
    BINARY_FORMAT=BASE64
  )

And the following using 23.12.2:

✗ python test_sqlglot.py       
Traceback (most recent call last):
  File "/Users/dangoldin/code/analysis/test_sqlglot.py", line 494, in <module>
    pq = parse_one(q, read="snowflake")
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/__init__.py", line 139, in parse_one
    result = dialect.parse(sql, **opts)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/dialects/dialect.py", line 506, in parse
    return self.parser(**opts).parse(self.tokenize(sql), sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 1175, in parse
    return self._parse(
           ^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 1241, in _parse
    expressions.append(parse_method(self))
                       ^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 1470, in _parse_statement
    return self.STATEMENT_PARSERS[self._prev.token_type](self)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 631, in <lambda>
    TokenType.COPY: lambda self: self._parse_copy(),
                                 ^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 6363, in _parse_copy
    files = self._parse_csv(self._parse_conjunction)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 5649, in _parse_csv
    parse_result = parse_method()
                   ^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 3808, in _parse_conjunction
    return self._parse_tokens(self._parse_equality, self.CONJUNCTION)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 5663, in _parse_tokens
    this = parse_method()
           ^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 3811, in _parse_equality
    return self._parse_tokens(self._parse_comparison, self.EQUALITY)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 5670, in _parse_tokens
    expression=parse_method(),
               ^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 3814, in _parse_comparison
    return self._parse_tokens(self._parse_range, self.COMPARISON)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 5663, in _parse_tokens
    this = parse_method()
           ^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 3817, in _parse_range
    this = this or self._parse_bitwise()
                   ^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 3941, in _parse_bitwise
    this = self._parse_term()
           ^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 3973, in _parse_term
    return self._parse_tokens(self._parse_factor, self.TERM)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 5663, in _parse_tokens
    this = parse_method()
           ^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 3977, in _parse_factor
    this = parse_method()
           ^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 3998, in _parse_unary
    return self._parse_at_time_zone(self._parse_type())
                                    ^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 4020, in _parse_type
    this = self._parse_column()
           ^^^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 4220, in _parse_column
    this = self._parse_column_reference()
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 4224, in _parse_column_reference
    this = self._parse_field()
           ^^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 4347, in _parse_field
    field = self._parse_primary() or self._parse_function(
            ^^^^^^^^^^^^^^^^^^^^^
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 4330, in _parse_primary
    self._match_r_paren(expression=this)
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 6196, in _match_r_paren
    self.raise_error("Expecting )")
  File "/Users/dangoldin/.virtualenvs/lib/python3.11/site-packages/sqlglot/parser.py", line 1285, in raise_error
    raise error
sqlglot.errors.ParseError: Expecting ). Line 5, Col: 17.
  
copy into temp FROM @random_stage/path/
FILE_FORMAT = (
    TYPE=CSV
    FIELD_DELIMITER='|'
    NULL_IF=()
    FIELD_OPTIONALLY_ENCLOSED_BY='"'
    TIMESTAMP_FORMAT='TZHTZM YYYY-MM-DD HH24

Official Documentation
Please include links to official SQL documentation related to your issue.
@VaggelisD VaggelisD self-assigned this May 1, 2024
@georgesittas
Copy link
Collaborator

Sorry for the trouble @dangoldin, this was an oversight on our end. Should have deployed a minor version. We'll fix this soon and look into deploying a patch.

@dangoldin
Copy link
Contributor Author

All good and appreciate the responsiveness. I just fell back to 23.12.1 in my code and would attempt a fix here but haven't been keeping up with the changes here so it was quicker to quickly file this.

@georgesittas
Copy link
Collaborator

georgesittas commented May 1, 2024 •
edited

No worries. This is probably an edge case, because we do have some logic that tries to parse this into a Command as a fallback case, so it fails somewhere in between.

@georgesittas georgesittas mentioned this issue May 2, 2024
Merged
@georgesittas
Copy link
Collaborator

FYI @dangoldin just deployed v23.13.1

@dangoldin
Copy link
Contributor Author

Awesome! Thanks for the quick turn around on this.

@marianysilva
Copy link

marianysilva commented May 14, 2024 •
edited

Hey @georgesittas I had the same error message but with REMOVE SQL clausula.

REMOVE @STAGE_NAME/path/path contains unsupported syntax. Falling back to parsing as a 'Command'.

Screenshot 2024-05-14 at 14 58 05

@georgesittas
Copy link
Collaborator

This is expected, REMOVE is unsupported syntax.

@marianysilva
Copy link

marianysilva commented May 14, 2024 •
edited

Not sure if I get wrong... but Snowflake examples

@georgesittas
Copy link
Collaborator

You're right, apologies for not clarifying. I meant that this statement is not supported by SQLGlot.

@marianysilva
Copy link

marianysilva commented May 14, 2024 •
edited

Oh, okay. TY. ❤️ 💻
Do you know if SQLGlot has plans to support it? It seems we already have some support to REMOVE here

@georgesittas
Copy link
Collaborator

georgesittas commented May 14, 2024 •
edited

We don't plan to do this soon, but well-crafted & tested PRs are welcome.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@VaggelisD VaggelisD

Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

fix(snowflake): COPY postfix tobymao/sqlglot
4 participants
@dangoldin @VaggelisD @georgesittas @marianysilva