Table extraction excludes CREATE TABLE target

[karakanb]

build_scope does not include the target table of a CREATE TABLE AS SELECT statement. sqlglot includes it.

sqlglot

# /// script
# requires-python = ">=3.11"
# dependencies = ["sqlglot"]
# ///

from sqlglot import exp, parse

sql = "CREATE TABLE out_table AS SELECT 1 AS id"
tables = [t.name for stmt in parse(sql, dialect="bigquery") for t in stmt.find_all(exp.Table)]

print(f"SQL:    {sql}")
print(f"Tables: {tables}")

polyglot repro

use polyglot_sql::{self as pgsql, DialectType};

fn main() {
    let sql = "CREATE TABLE out_table AS SELECT 1 AS id";
    let expr = &pgsql::parse(sql, DialectType::BigQuery).unwrap()[0];
    let scope = pgsql::scope::build_scope(expr);
    let tables: Vec<&String> = scope.sources.keys().collect();

    println!("SQL:    {}", sql);
    println!("Tables: {:?}", tables);
}

[tobilg]

Thanks for reporting! This will land in version 0.1.12.

[MartinSahlen]

To me this feels a bit like a matter of "taste", or it's probably also due to how both sqlglot and this library lingers between semantics, validation and pure syntax. If you would do this in something like a more proper/formal library like GoogleSQL/ZetaSQL, it clearly differs between i.e a Scan node and i.e just the name of the table being created. I.e is out_table a source in this context? if I would be using this library i'm not sure I would use it like this, I would want process the result statement, see that it's a create statement, get the target table name and then figure out the sources, ie dependencies from the select statement. Because now it gives the impression that all these tables must exist for the statement to run, but the target table doesn need that. Or maybe I'm misunderstanding the intention / use case here.