sqlparser-rs
Snowflake: TIMESTAMP precision regression
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
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 https://github.com/apache/datafusion-sqlparser-rs/commit/91327bb0c02a09e0b5c5322c813c4b2a3b564439, 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
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
}
}
]