Surprising SQL

In Oracle SQL statements it is not allowed to use reserved words as identifiers. For example, it would confuse the SQL parser – and us as well – to name a table as TABLE or a column as SELECT. Of course, it is possible to use such names when the name is written in double quotes, but a statement like CREATE TABLE “TABLE” is definitely not a good practice.

A list of reserved words can be found in the appendix of the Oracle SQL Reference manual. A few days ago I used this list for the first time since many years and was surprised to see that all the keywords of the ANSI join syntax are missing on this list. Were they just forgotten in the documentation, or is it allowed to use the words LEFT, RIGHT, INNER, OUTER, FULL and JOIN as identifiers?

Although this question is not really relevant for real projects, I wanted to know how the SQL parser of Oracle 11.2 handles these keywords. Train journeys are always a good chance to do such things, and at the moment I’m traveling from Munich to Zurich by train and have time to do some tests with SQL. I’m a bit surprised that a statement like this is not only syntactically correct, but also returns the right result:

SELECT * FROM LEFT JOIN RIGHT ON LEFT.OUTER = RIGHT.JOIN
LEFT JOIN OUTER ON OUTER.JOIN = LEFT.RIGHT

You agree that this statement is a bit hard to read, but it seems that the SQL parser can handle it without problems. When we reformat the statement as shown in the test case below, it looks at least more readable, but still confusing. If you want to check whether this really works, here my test case:

-- Original statment:
SELECT *
  FROM emp
  JOIN dept ON emp.deptno = dept.deptno
  LEFT JOIN bonus ON bonus.ename = emp.ename;

-- Prepare test case:
CREATE TABLE LEFT AS SELECT * FROM emp;
CREATE TABLE RIGHT AS SELECT * FROM dept;
CREATE TABLE OUTER AS SELECT * FROM bonus;
ALTER TABLE LEFT RENAME COLUMN deptno TO OUTER;
ALTER TABLE RIGHT RENAME COLUMN deptno TO JOIN;
ALTER TABLE OUTER RENAME COLUMN ename TO JOIN;
ALTER TABLE LEFT RENAME COLUMN ename TO RIGHT;

-- Statement for test case:
SELECT *
  FROM LEFT
  JOIN RIGHT ON LEFT.OUTER = RIGHT.JOIN
  LEFT JOIN OUTER ON OUTER.JOIN = LEFT.RIGHT;

And what did I learn from this test case? First, SQL still contains some surprises, although I thought I know this query language since years. Second, I should avoid long train journeys – otherwise I will have more silly ideas for tests like this.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s