nested with statement causes confusion

[tipanverella]

I wrote the following test:

def test_create_table_as_select_with_joins_with():
    qry = """
        CREATE table xyz as 
        with sub as (select it_id from internal_table)
        SELECT *
        from (
            with abc as (select * from other_table)
            select name, age, it_id
            from table_z
            join abc on (table_z.it_id = abc.it_id)
        ) as table_a
        join table_b on (table_a.name = table_b.name)
        left join table_c on (table_a.age = table_c.age)
        left join sub on (table_a.it_id = sub.it_id)
        order by table_a.name, table_a.age
        """
    parser = Parser(qry)
    assert set(parser.tables) == set(["xyz", "table_z", "table_b", "table_c", "internal_table", "other_table"])

which the code is unable to pass, as it thinks with in the following sub-query:

from (
  with abc as (select * from other_table)
  select name, age, it_id
  from table_z
  join abc on (table_z.it_id = abc.it_id)
)

is a table.

[tipanverella]

Please let me know if there is anything I can do to help.

[collerek]

@tipanverella You just have to put that with everywhere, don't you? 🤣

[macbre]

S in SQL does not stand for simple for sure 😉

[tipanverella]

S in SQL does not stand for simple for sure

It stands for structured, I don't make the rules!!! 🤣

[tipanverella]

But seriously, please let me know if I can help in any other way. You folks are doing good work.

[collerek]

@tipanverella fix for this is already in a PR waiting for @macbre review.

You are already helping with those failing edge cases, thanks to you sql-metadata is becoming better and more universal☺️