ParseError when table name is prefixed with a # (Redshift)

[yaseenlotfi]

Hey, just got started using sqlglot and find it quite easy and idiomatic to use.

I just encountered a parsing issue when a table is prefixed with a #. I was kind of surprised since this tends to be common practice when working with temp tables across multiple dialects. Thought it would merit some discussion on how to tackle this before raising an issue to track.

Imagine a scenario where a temp table was created called #table, potentially as part of a SQL script with many statements in a transaction block. The final query at the end of the script could be something like this:

from sqlglot import parse_one

QUERY = """
with cte as (
    select a, b, c
    from #table
)
select cte.*, t1.foo
from cte
join myschema.other_table t1
on cte.a = t1.foo
"""

parsed = parse_one(QUERY, read="redshift") 

Error:

sqlglot.errors.ParseError: Expected table name but got <Token token_type: TokenType.HASH, text: #, line: 4, col: 10, comments: []>. Line 4, Col: 10.

What would be some ways to handle this?

Can I modify this parsing behavior with the Dialect interface e.g. modifying Redshift or subclassing it? Does something need to change deeper under the hood with how the parser works?

At a high level, when the parser expects a Table, it should also allow for a Hash token as part of the Table Name (I think T-SQL even allows for double hashes iirc).

I'm new to working with ASTs, let alone new to this specific project, but open to working on a PR, if applicable.

[georgesittas]

Hey, welcome and thanks for taking the time to report this issue! There was an easy fix for this so I went ahead and pushed it already (cc7691c). We'd already implemented this logic for T-SQL (https://github.com/tobymao/sqlglot/blob/main/sqlglot/dialects/tsql.py#L271).

[yaseenlotfi]

Awesome, this worked for Redshift as well.

Just patched it as such:

class RedshiftPatched(Redshift):
    class Tokenizer(Redshift.Tokenizer):
        SINGLE_TOKENS = tokens.Tokenizer.SINGLE_TOKENS.copy()
        SINGLE_TOKENS.pop("#")

...

parsed = parse_one(QUERY, read="redshiftpatched")

Does it make sense to add the same two line change to the base Redshift class for a quick PR or should we figure out all the dialects this applies to and have a generalized flag or something which can be handled by each dialect? Honestly, I'm only aware of this being a thing for Redshift and T-SQL.

e.g. base Dialect has a bool flag, default False, whether to support special characters in table name or not. If true, modify the Tokenizer like above, else leave it be.

[georgesittas]

Already pushed a fix for this in main, so no need to make a PR. This change should be picked up in the next release.

I'm personally unaware of other dialects that support this syntax, so for the time being I'd say it's ok to leave it like this. Feel free to investigate though; if you find any other dialect we can chat on how this can be refactored (if needed).