Snowflake: TIMESTAMP precision regression

[psukys]

For Snowflake time formats support precision parameter (from Snowflake documentation):

All timestamp variations, as well as the TIMESTAMP alias, support an optional precision parameter for fractional seconds (for example, TIMESTAMP(3)). Timestamp precision can range from 0 (seconds) to 9 (nanoseconds). The default precision is 9.

Note: this comes from using Python wrapper sqloxide

Query that fails:

create table hello(test timestamp_ntz(6))

Error:

sql parser error: Expected: ',' or ')' after column definition, found: ( at Line: 1, Column: 38

removing precision from timestamp_ntz passes through

Unfortunately I cannot directly link which version (not visible throughintroduced this regression, but it seems to be recent

[nucccc]

I'm trying to take a look into that, and I'm trying to evaluate if a new type shall be declared inside src/ast/data_type.rs or an existing one shall be reused.

For example there is

    /// Datetime with optional time precision, see [MySQL][1].
    ///
    /// [1]: https://dev.mysql.com/doc/refman/8.0/en/datetime.html
    Datetime(Option<u64>),

but it has a different name, and snowflake seems to have several timestamp types all with precision. I don't know if it is appropriate to "augment" TimestampNtz with a precision

    /// Databricks timestamp without time zone. See [1].
    ///
    /// [1]: https://docs.databricks.com/aws/en/sql/language-manual/data-types/timestamp-ntz-type
    TimestampNtz,

since afterall it already refers to Databricks.

I think that once a proper datatype is defined it could be possible to modify parse_data_type_helper from src/parser/mod.rs to account for that type.

In any case I saw that support for TIMESTAMP_NTZ was added in 91327bb, but I don't know how the code would behave before, also because that commit seems to have introduced the TIMESTAMP_NTZ keyword, that before was not existing

[psukys]

Is TimestampNtz shared between Databricks and Snowflake?
Checking on slightly older version (sqloxide==0.1.54), the parsed result is:

[
    {
        "CreateTable": {
            "or_replace": false,
            "temporary": false,
            "external": false,
            "global": null,
            "if_not_exists": false,
            "transient": false,
            "volatile": false,
            "name": [
                {
                    "value": "hello",
                    "quote_style": null,
                    "span": {
                        "start": {
                            "line": 1,
                            "column": 14
                        },
                        "end": {
                            "line": 1,
                            "column": 19
                        }
                    }
                }
            ],
            "columns": [
                {
                    "name": {
                        "value": "test",
                        "quote_style": null,
                        "span": {
                            "start": {
                                "line": 1,
                                "column": 20
                            },
                            "end": {
                                "line": 1,
                                "column": 24
                            }
                        }
                    },
                    "data_type": {
                        "Custom": [
                            [
                                {
                                    "value": "timestamp_ntz",
                                    "quote_style": null,
                                    "span": {
                                        "start": {
                                            "line": 1,
                                            "column": 25
                                        },
                                        "end": {
                                            "line": 1,
                                            "column": 38
                                        }
                                    }
                                }
                            ],
                            [
                                "6"
                            ]
                        ]
                    },
                    "collation": null,
                    "options": []
                }
            ],
            "constraints": [],
            "hive_distribution": "NONE",
            "hive_formats": {
                "row_format": null,
                "serde_properties": null,
                "storage": null,
                "location": null
            },
            "table_properties": [],
            "with_options": [],
            "file_format": null,
            "location": null,
            "query": null,
            "without_rowid": false,
            "like": null,
            "clone": null,
            "engine": null,
            "comment": null,
            "auto_increment_offset": null,
            "default_charset": null,
            "collation": null,
            "on_commit": null,
            "on_cluster": null,
            "primary_key": null,
            "order_by": null,
            "partition_by": null,
            "cluster_by": null,
            "clustered_by": null,
            "options": null,
            "strict": false,
            "copy_grants": false,
            "enable_schema_evolution": null,
            "change_tracking": null,
            "data_retention_time_in_days": null,
            "max_data_extension_time_in_days": null,
            "default_ddl_collation": null,
            "with_aggregation_policy": null,
            "with_row_access_policy": null,
            "with_tags": null
        }
    }
]