BigQuery partitioning, clustering, options fail hard

[MartinSahlen]

A totally normal query like this simple example

use polyglot_sql::{DialectType, parse_one};

fn main() {
    const query: &str = r#"
   create or replace table `p1`.`d1`.`t1`

    partition by date_trunc(day, month)
    cluster by some_cluster_column

    OPTIONS(
      description="""""",

      labels=[('l1', 'v'), ('l2', 'v2'), ('l3', 'v3'), ('l4', 'v5')]
    )
    as (SELECT current_date as day, DATE_TRUNC(current_date(), month) as month, 'c' as some_cluster_column)
 "#;

    let rest = parse_one(query, DialectType::BigQuery).unwrap();
}

yields

thread 'main' (38353401) panicked at src/main.rs:18:56:
called `Result::unwrap()` on an `Err` value: Parse { message: "Expected LParen, got Partition ('partition') near [d1.t1     partition by date_trunc(]", line: 4, column: 14 }
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace

I have not dived deep into the code, but feels like this could be needing quite extensive review of https://docs.cloud.google.com/bigquery/docs/creating-partitioned-tables and creating/updating tables. This is a simplified real world example. it works fine in sqlparser-rs and runs fine in BigQuery editor.

[MartinSahlen]

I would relly like to have the similar structure as in sqlparser-rs where all these options and configs are collected and structured programaticallt into clean rust structs.

[tobilg]

I‘ll have a look, thanks!

[tobilg]

I would relly like to have the similar structure as in sqlparser-rs where all these options and configs are collected and structured programaticallt into clean rust structs.

Could you give an example? You meant for error reporting?

[MartinSahlen]

No, I meant the AST itself, i.e this one is a good example of CreateTable

https://github.com/apache/datafusion-sqlparser-rs/blob/bd7f70e82048cab324dba6224fb3dd17757d8477/src/ast/ddl.rs#L2887

As the parser eats away at the tokens, it populates these rich structs, ie the screenshot here shows the partition by. That being said maybe

it does seem that I didnt read the code here (

polyglot/crates/polyglot-sql/src/expressions.rs

Line 5193 in 1097036

pub struct CreateTable {

) good enough . so it seems a very similar structure is already present. so I guess it's about putting it all in there.

[tobilg]

Understood, thanks. I already implemented a fix for this (and a few others things that weren't covered). Will be released in version 0.1.10.

[MartinSahlen]

Great, feel free to use my example as a test case :D

[tobilg]

Version 0.1.10 was released.

[MartinSahlen]

Would it be possible to also create some more semantic structure vs "Raw" literals? also seeing the options themselves being treated as pretty raw expressions vs some more easily usable structure. Not immediately critical, just a thought, this is what makes the slqparser-rs so nice to work with, that it prepares all of this.

✅ Parse succeeded!
CreateTable(
    CreateTable {
        name: TableRef {
            name: Identifier {
                name: "t1",
                quoted: true,
                trailing_comments: [],
                span: None,
            },
            schema: Some(
                Identifier {
                    name: "d1",
                    quoted: true,
                    trailing_comments: [],
                    span: None,
                },
            ),
            catalog: Some(
                Identifier {
                    name: "p1",
                    quoted: true,
                    trailing_comments: [],
                    span: None,
                },
            ),
            alias: None,
            alias_explicit_as: false,
            column_aliases: [],
            trailing_comments: [],
            when: None,
            only: false,
            final_: false,
            table_sample: None,
            hints: [],
            system_time: None,
            partitions: [],
            identifier_func: None,
            changes: None,
            version: None,
            span: None,
        },
        on_cluster: None,
        columns: [],
        constraints: [],
        if_not_exists: false,
        temporary: false,
        or_replace: true,
        table_modifier: None,
        as_select: Some(
            Subquery(
                Subquery {
                    this: Select(
                        Select {
                            expressions: [
                                Alias(
                                    Alias {
                                        this: Function(
                                            Function {
                                                name: "current_date",
                                                args: [],
                                                distinct: false,
                                                trailing_comments: [],
                                                use_bracket_syntax: false,
                                                no_parens: true,
                                                quoted: false,
                                                span: None,
                                            },
                                        ),
                                        alias: Identifier {
                                            name: "day",
                                            quoted: false,
                                            trailing_comments: [],
                                            span: None,
                                        },
                                        column_aliases: [],
                                        pre_alias_comments: [],
                                        trailing_comments: [],
                                    },
                                ),
                                Alias(
                                    Alias {
                                        this: Function(
                                            Function {
                                                name: "DATE_TRUNC",
                                                args: [
                                                    Function(
                                                        Function {
                                                            name: "current_date",
                                                            args: [],
                                                            distinct: false,
                                                            trailing_comments: [],
                                                            use_bracket_syntax: false,
                                                            no_parens: false,
                                                            quoted: false,
                                                            span: None,
                                                        },
                                                    ),
                                                    Column(
                                                        Column {
                                                            name: Identifier {
                                                                name: "month",
                                                                quoted: false,
                                                                trailing_comments: [],
                                                                span: None,
                                                            },
                                                            table: None,
                                                            join_mark: false,
                                                            trailing_comments: [],
                                                            span: None,
                                                        },
                                                    ),
                                                ],
                                                distinct: false,
                                                trailing_comments: [],
                                                use_bracket_syntax: false,
                                                no_parens: false,
                                                quoted: false,
                                                span: None,
                                            },
                                        ),
                                        alias: Identifier {
                                            name: "month",
                                            quoted: false,
                                            trailing_comments: [],
                                            span: None,
                                        },
                                        column_aliases: [],
                                        pre_alias_comments: [],
                                        trailing_comments: [],
                                    },
                                ),
                                Alias(
                                    Alias {
                                        this: Literal(
                                            String(
                                                "c",
                                            ),
                                        ),
                                        alias: Identifier {
                                            name: "some_cluster_column",
                                            quoted: false,
                                            trailing_comments: [],
                                            span: None,
                                        },
                                        column_aliases: [],
                                        pre_alias_comments: [],
                                        trailing_comments: [],
                                    },
                                ),
                            ],
                            from: None,
                            joins: [],
                            lateral_views: [],
                            prewhere: None,
                            where_clause: None,
                            group_by: None,
                            having: None,
                            qualify: None,
                            order_by: None,
                            distribute_by: None,
                            cluster_by: None,
                            sort_by: None,
                            limit: None,
                            offset: None,
                            limit_by: None,
                            fetch: None,
                            distinct: false,
                            distinct_on: None,
                            top: None,
                            with: None,
                            sample: None,
                            settings: None,
                            format: None,
                            windows: None,
                            hint: None,
                            connect: None,
                            into: None,
                            locks: [],
                            for_xml: [],
                            leading_comments: [],
                            post_select_comments: [],
                            kind: None,
                            operation_modifiers: [],
                            qualify_after_window: false,
                            option: None,
                        },
                    ),
                    alias: None,
                    column_aliases: [],
                    order_by: None,
                    limit: None,
                    offset: None,
                    distribute_by: None,
                    sort_by: None,
                    cluster_by: None,
                    lateral: false,
                    modifiers_inside: false,
                    trailing_comments: [],
                },
            ),
        ),
        as_select_parenthesized: false,
        on_commit: None,
        clone_source: None,
        clone_at_clause: None,
        is_copy: false,
        shallow_clone: false,
        leading_comments: [],
        with_properties: [],
        teradata_post_name_options: [],
        with_data: None,
        with_statistics: None,
        teradata_indexes: [],
        with_cte: None,
        properties: [
            Raw(
                Raw {
                    sql: "PARTITION BY DATE_TRUNC(day, month)",
                },
            ),
            Raw(
                Raw {
                    sql: "CLUSTER BY some_cluster_column",
                },
            ),
            Properties(
                Properties {
                    expressions: [
                        Eq(
                            BinaryOp {
                                left: Column(
                                    Column {
                                        name: Identifier {
                                            name: "description",
                                            quoted: false,
                                            trailing_comments: [],
                                            span: None,
                                        },
                                        table: None,
                                        join_mark: false,
                                        trailing_comments: [],
                                        span: None,
                                    },
                                ),
                                right: Literal(
                                    String(
                                        "",
                                    ),
                                ),
                                left_comments: [],
                                operator_comments: [],
                                trailing_comments: [],
                            },
                        ),
                        Eq(
                            BinaryOp {
                                left: Column(
                                    Column {
                                        name: Identifier {
                                            name: "labels",
                                            quoted: false,
                                            trailing_comments: [],
                                            span: None,
                                        },
                                        table: None,
                                        join_mark: false,
                                        trailing_comments: [],
                                        span: None,
                                    },
                                ),
                                right: ArrayFunc(
                                    ArrayConstructor {
                                        expressions: [
                                            Tuple(
                                                Tuple {
                                                    expressions: [
                                                        Literal(
                                                            String(
                                                                "l1",
                                                            ),
                                                        ),
                                                        Literal(
                                                            String(
                                                                "v",
                                                            ),
                                                        ),
                                                    ],
                                                },
                                            ),
                                            Tuple(
                                                Tuple {
                                                    expressions: [
                                                        Literal(
                                                            String(
                                                                "l2",
                                                            ),
                                                        ),
                                                        Literal(
                                                            String(
                                                                "v2",
                                                            ),
                                                        ),
                                                    ],
                                                },
                                            ),
                                            Tuple(
                                                Tuple {
                                                    expressions: [
                                                        Literal(
                                                            String(
                                                                "l3",
                                                            ),
                                                        ),
                                                        Literal(
                                                            String(
                                                                "v3",
                                                            ),
                                                        ),
                                                    ],
                                                },
                                            ),
                                            Tuple(
                                                Tuple {
                                                    expressions: [
                                                        Literal(
                                                            String(
                                                                "l4",
                                                            ),
                                                        ),
                                                        Literal(
                                                            String(
                                                                "v5",
                                                            ),
                                                        ),
                                                    ],
                                                },
                                            ),
                                        ],
                                        bracket_notation: true,
                                        use_list_keyword: false,
                                    },
                                ),
                                left_comments: [],
                                operator_comments: [],
                                trailing_comments: [],
                            },
                        ),
                    ],
                },
            ),
        ],
        partition_of: None,
        post_table_properties: [],
        mysql_table_options: [],
        inherits: [],
        on_property: None,
        copy_grants: false,
        using_template: None,
        rollup: None,
    },
)