6

Section 5.6 of the SQL-92 standard contains rules 10...13 per which unquoted identifiers should be upper-cased, so foo becomes FOO but "foo" remains foo.

These rules are respected by Oracle, IBM DB2, Snowflake, and ksqlDB but not by Postgres, MySQL or SQLite, for example.

The question is, why? In my understanding, the optional quoting of identifiers in a language with a lot of keywords makes sense. Consistent case sensitivity or insensitivity of identifiers would also make sense. But making it dependent on the identifier being quoted doesn't look rational.

What am I missing?

3 Answers 3

8

But making it dependent on the identifier being quoted doesn't look rational

Case-sensitive identifiers suck. Imagine the chaos of having tables named Foo, FOO, and foo, and all the frustrating erros of users who write SQL queries and get the case of objects wrong. But some older RDBMS system were inherently case-sensitive (Oracle I'm looking at you).

So transforming identifiers to all caps is an old-fashioned workaround to not having the ability to support a case-insensitive catalog.

So create table Foo ... actually creates a table named "FOO" and in a SQL Query select * from foo the object name is uppercased before searching for it in the catalog.

But having support for mixed case identifiers is an important feature. So if regular identifiers are implicitly converted to all caps, it makes some sense to allow mixed case identifiers if explicitly delimited with ".

For instance SQL Server has mixed case identifiers, and an application might rely on reading the mixed case identifiers from the catalog. This is possible in Oracle by creating the table with mixed case quoted identifiers.

Also this describes the actual behavior of Oracle, which was one of the only RDBMS systems around back then. So it also may be that the the Oracle design is being codified by the SQL standard.

3
  • The answer quotes the part of the question with the "quoted" but doesn't seem to answer the question. Upper-casing identifiers to emulate case insensitivity makes sense but why does that depend on the identifier being quoted? Commented Jan 7, 2023 at 20:03
  • See updated answer. Commented Jan 7, 2023 at 20:53
  • So by implementing the upper-casing of the unquoted identifiers, Oracle introduced a BC break: in order to keep using mixed-case identifiers, the users would have to rewrite their code. Wouldn't it be less confusing to recommend changing identifiers in another way? E.g. rename foo and FOO to FOO_1 and FOO_2 respectively. This would mean the same effort for the users but less confusing behavior for the database. Commented Jan 13, 2023 at 3:31
4

Originally, the quotes were not used. This meant that identifiers could not use reserved names such as index and table. And case of identifiers was irrelevant. However, the convention was to use uppercase identifiers not just in the database but also in many programming languages.

Later the quotes were allowed, making possible the ability to use mixed case identifiers as well as reserved names. On some databases, you can choose which option you want to use.

The sql-92 standard has been a little ambiguous allowing different languages to behave a little differently. There are quirks within a language such as Firebird. Older ones such as Oracle have had to respect legacy usage.

As an example in Firebird/Interbase, say one is using dialect 3 (telling the database that quoted identifiers will be used). If an identifier is left unquoted it is treated as dialect-1 identifier. Theoretically, it should be upper case, but the case is actually irrelevant.

Some languages may insist on upper case identifiers being upper case. But all this is because of historical/legacy support and/or newer decisions they have had to make in their design. It would appear that Some have decided to make it easier for the user, whereas others are adhering strictly to the standard.

Standards

It would be useful to remember that the languages were not originally designed to comply with a standard, let alone sql-92. They were adapted to comply with the standard. So, implementations vary a little bit.

2
  • This seems to contradict the first answer which says that case sensitivity sucks and the upper-casing was added as a workaround for the case sensitivity of the catalog. Why would the database designers want to apply this workaround only to the unquoted identifiers? Commented Jan 7, 2023 at 20:12
  • Actually, the answer was half an hour later than mine. I have updated the answer, having lived through the transition I am aware of the history. Also, different designers can interpret standards differently, especially as they have had to progress through the changes. Commented Jan 7, 2023 at 21:30
2

Why are unquoted identifiers upper-cased per SQL-92?

Likely for historical reasons: when the SQL language was developed, many computer systems were only capable of accepting, processing, and outputting uppercase letters. Later, when terminals and output devices began supporting larger character sets that included lowercase letters, it was only natural to fold them by default to the upper case for backward compatibility.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.