Snowflake and Reserved Words

One of the things that fascinates me about SQL is the extreme limits on reserved words, especially when compared with most procedural languages. I’ve written about using reserved words for IBM Db2 and MySQL. I thought I’d explore the issue for Snowflake.

Note that I’m using snowSQL – the command line interface for Snowflake – for the actions in this blog article. This is mostly to keep things fresh for myself and because I miss the command line a bit. My Vi skills are rusty!

Reserved Words

Much like other platforms I have worked with, Snowflake has a list of reserved words. When exploring reserved words in IBM Db2, I was a bit surprised that this statement worked:

db2 "create table from (select varchar(10), and varchar(10), where varchar(10))"
DB20000I  The SQL command completed successfully.

This allows the success of this seemingly non-sensical query:

$ db2 "select select from from where where='' and and=''"

SELECT
----------

  0 record(s) selected.

Some of these are reserved words in Db2, but Db2 doesn’t enforce the use of delimiters unless there is some confusion possible around what is meant by a reserved word.

In Snowflake, if I try this, I get an error:

emberc#COMPUTE_WH@ECROOKS_EXP.PUBLIC>create table from (select varchar(10), and varchar(10), where varchar(10));
001003 (42000): SQL compilation error:
syntax error line 1 at position 13 unexpected 'from'.

This doesn’t mean I can’t do the same thing, I just have to delimit my identifiers:

emberc#COMPUTE_WH@ECROOKS_EXP.PUBLIC>create table "from" ("select" varchar(10), "and" varchar(10), "where" varchar(10));
+----------------------------------+
| status                           |
|----------------------------------|
| Table from successfully created. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.416s

Note that the delimiter needed here " is different than the delimiter I needed for MySQL `.

I do have to use the delimiters every time I use these identifiers – in queries and so forth. This is not surprising – when using reserved words in unsupported ways, one should expect some oddities.

I respect Snowflake’s choice to force the use of delimiters for reserved words – it makes a lot of sense to me.

Capitalization

Playing with things like this makes me revisit capitalization rules for object names as well. I find any table name that requires delimiters mildly offensive and slightly evil, even though nearly every script I wrote in my past life as a DBA accounted for them. In my past article on Db2 and MySQL, I noted that Db2 stores the name of the table in upper case internally, and if using delimiters around an object, required case to also match. MySql on the other hand seemed to ignore case, even when the object name was delimited. Let’s do the same experiments in Snowflake.

emberc#COMPUTE_WH@ECROOKS_EXP.PUBLIC>create table table_1 (ThisColumn varchar(10));
+-------------------------------------+
| status                              |
|-------------------------------------|
| Table TABLE_1 successfully created. |
+-------------------------------------+
1 Row(s) produced. Time Elapsed: 0.719s
emberc#COMPUTE_WH@ECROOKS_EXP.PUBLIC>describe table table_1;
+------------+-------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+
| name       | type        | kind   | null? | default | primary key | unique key | check | expression | comment | policy name | privacy domain |
|------------+-------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------|
| THISCOLUMN | VARCHAR(10) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
+------------+-------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+
1 Row(s) produced. Time Elapsed: 0.269s

We can see here that though we specified the column name in mixed case, it is stored in upper case, just like Db2.
Again, much like Db2, we need to match case within delimiters:

emberc#COMPUTE_WH@ECROOKS_EXP.PUBLIC>select ThisColumn from table_1;
+------------+
| THISCOLUMN |
|------------|
+------------+
0 Row(s) produced. Time Elapsed: 0.317s
emberc#COMPUTE_WH@ECROOKS_EXP.PUBLIC>select "ThisColumn" from table_1;
000904 (42000): SQL compilation error: error line 1 at position 7
invalid identifier '"ThisColumn"'

This isn’t surprising to me, as I believe this is how Oracle works as well, and the founders of Snowflake had their roots at Oracle. I think that MySQL is the odd one out in the database world on this issue, though it is certainly possible other platforms that I haven’t worked with extensively behave similarly.

Summary

I enjoy seeing the way different database platforms solve common problems. I had a lot of fun writing this article, just because of the playfulness and exploration of the topic.