Table Options formatted like Field option

[dang1507]

I found an oddity in the formatting when using BIGQUERY syntax. When creating tables with metadata, the PARTITION BY and CLUSTER BY come before the OPTIONS(\<TABLE OPTIONS>). The formatter does not add a new line as it should because this syntax mirrors field options.

Actual

CREATE OR REPLACE TABLE `project.DATASET.MYNEWTABLE` (
    MYNUMBER INT64 NOT NULL DEFAULT 0 OPTIONS(DESCRIPTION = "my favorite number"),
    MYSTRING STRING NOT NULL DEFAULT "hello world" OPTIONS(DESCRIPTION = "my favorite string"),
    MYDOUBLE DOUBLE (10, 2) NOT NULL DEFAULT 0.0 OPTIONS(DESCRIPTION = "my favorite double with precision"),
    MYFLOAT FLOAT64 NOT NULL DEFAULT 0.0 OPTIONS(DESCRIPTION = "my favorite float"),
    MYBIGINT BIGINT OPTIONS(DESCRIPTION = "my favorite big number"),
    MYDATE DATE OPTIONS(DESCRIPTION = "my favorite date"),
    PRIMARY KEY (MYNUMBER, MYDATE) NOT ENFORCED,
    FOREIGN KEY (MYDOUBLE) REFERENCES `project.DATASET.MYDOUBLETABLE` (MYDOUBLE) NOT ENFORCED,
    FOREIGN KEY (MYFLOAT) REFERENCES `project.DATASET.ROOT` (BEERFLOAT) NOT ENFORCED
)
PARTITION BY
    MYNUMBER RANGE_BUCKET(0, 100000)
CLUSTER BY
    MYSTRING,
    MYNUMBER OPTIONS(  -- I am not a field option. I am a table option and should be on a new line
        DESCRIPTION = "test format fun",
        LABELS = [("format", "test")],
        REQUIRE_PARTITION = TRUE,
        EXPIRATION_TIMESTAMP = CURRENT_TIMESTAMP + INTERVAL 1 DAY
    ) AS

SELECT
    *
FROM
    `project.DATASET.FAKE_TABLE`
;

Corrected

CREATE OR REPLACE TABLE `project.DATASET.MYNEWTABLE` (
    MYNUMBER INT64 NOT NULL DEFAULT 0 OPTIONS(DESCRIPTION = "my favorite number"),
    MYSTRING STRING NOT NULL DEFAULT "hello world" OPTIONS(DESCRIPTION = "my favorite string"),
    MYDOUBLE DOUBLE (10, 2) NOT NULL DEFAULT 0.0 OPTIONS(DESCRIPTION = "my favorite double with precision"),
    MYFLOAT FLOAT64 NOT NULL DEFAULT 0.0 OPTIONS(DESCRIPTION = "my favorite float"),
    MYBIGINT BIGINT OPTIONS(DESCRIPTION = "my favorite big number"),
    MYDATE DATE OPTIONS(DESCRIPTION = "my favorite date"),
    PRIMARY KEY (MYNUMBER, MYDATE) NOT ENFORCED,
    FOREIGN KEY (MYDOUBLE) REFERENCES `project.DATASET.MYDOUBLETABLE` (MYDOUBLE),
    FOREIGN KEY (MYFLOAT) REFERENCES `project.DATASET.ROOT` (BEERFLOAT)
)
PARTITION BY
    MYNUMBER RANGE_BUCKET(0, 100000)
CLUSTER BY
    MYSTRING,
    MYNUMBER 
OPTIONS( -- I belong here 
        DESCRIPTION = "test format fun",
        LABELS = [("format", "test")],
        REQUIRE_PARTITION = TRUE,
        EXPIRATION_TIMESTAMP = CURRENT_TIMESTAMP + INTERVAL 1 DAY
) AS

SELECT
    *
FROM `project.DATASET.FAKE_TABLE`
;

[nene]

It seems that you are not using prettier-plugin-sql-cst.

Because when I attempt to format this code I get a parse error. Mainly because of the PRIMARY KEY and FOREIGN KEY syntax, which was been added to BigQuery after I finished implementing support of it.

I suspect you're instead using either sql-formatter or prettier-plugin-sql (which uses sql-formatter under the hood).

I can only say that fixing the OPTIONS() formatting will likely not happen in sql-formatter because that formatter does not understand much about the context. So it has to either always format OPTIONS() with newline before it or always without the newline.

[nene]

Anyway, I'm closing this issue as it doesn't directly apply to prettier-plugin-sql-cst.

Thanks for reminding me about new syntax in BigQuery. I've opened a separate issue to fix that: https://github.com/nene/sql-parser-cst/issues/69

As you are using BigQuery, I do encourage you to give a try to this plugin though as BigQuery is one of the dialects that has the best support. For example it does format the above OPTIONS() correctly. Though for now it does crash for various other parts of the BigQuery syntax that's not yet supported.

you can try it out in the playground

[nene]

BTW, @dang1507 is the DOUBLE data type actually supported by BigQuery? I don't see it mentioned in the documentation. I think you intended to use DECIMAL as DOUBLE (10, 2) doesn't really make sense to me.

Also, the syntax you used for PARTITION BY:

PARTITION BY
    MYNUMBER RANGE_BUCKET(0, 100000)

Is this actually supported by BigQuery? It doesn't look like valid syntax to me.

[dang1507]

Nice catches. I was typing these out on the fly and didn't check the details. Mostly I was throwing out stuff I work with regularly and wanted to see how the various plugin formatters worked.

I want one based on prettier but with a few extra options.

Thank you for calling these out.

Corrected

Create...( MYNUMBER INT64 NOT NULL OPTIONS(...), My_total Decimal(10, 2) default 0.0 options(...) )

PARTITION BY RANGE_BUCKET(MYNUMBER, GENERATE_ARRAY(0, 100, 1))

Daniel

On Mon, Feb 5, 2024, 11:46 Rene Saarsoo @.***> wrote:

BTW, @dang1507 https://github.com/dang1507 is the DOUBLE data type actually supported by BigQuery? I don't see it mentioned in the documentation https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#numeric_types. I think you intended to use DECIMAL as DOUBLE (10, 2) doesn't really make sense to me.

Also, the syntax you used for PARTITION BY:

PARTITION BY MYNUMBER RANGE_BUCKET(0, 100000)

Is this actually supported by BigQuery? It doesn't look like valid syntax to me.

— Reply to this email directly, view it on GitHub https://github.com/nene/prettier-plugin-sql-cst/issues/22#issuecomment-1927442870, or unsubscribe https://github.com/notifications/unsubscribe-auth/AOL4NHGSGZJAK4FY3AN65V3YSEEIDAVCNFSM6AAAAABCYS5FYWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSMRXGQ2DEOBXGA . You are receiving this because you were mentioned.Message ID: @.***>

[nene]

Thanks.