ROWNUM and ROWID not considered reserved words

[Jason Baker]

ROWNUM and ROWID are not currently considered reserved words. These are pseudo-columns, so adding them as reserved words would make a query like this fail since ROWNUM would be escaped:

    SELECT * FROM(
        SELECT a, b, c FROM t;
    ) WHERE ROWNUM < 3

This has the side effect of making it difficult to do things like this:

    SQL> CREATE TABLE "ROWNUM" (a int, b int);

    Table created.

    SQL> SELECT * FROM "ROWNUM";

    no rows selected

A better solution would probably be to have an "allowed in WHERE" word list.

[James Henstridge (jamesh)]

If you want to use rownum as part of a generated expression inside the Oracle backend, you could do something like:

    from storm.expr import Column
    rownum = Column("ROWNUM")

Then you could use it in the expression you're generating.

As for supporting a real table or column called "ROWNUM", I wouldn't worry too much. Each of the backends have their quirks, and if Oracle doesn't like you creating tables called ROWNUM then so be it.

[Gustavo Niemeyer (niemeyer)]

Please note that it's very easy to add backend-specific reserved words so that Storm itself will escape these when used in columns and table names.

[Jason Baker (jbaker)]

@James - That's actually how the backend is doing it, but it still gets escaped. Although I do see your point. A table named "ROWNUM" would be kind of silly.

[James Henstridge (jamesh)]

Jason: I don't see ROWNUM in the reserved words list, so I wouldn't have expected it to be escaped at all during compilation. Using the base compile class:

    >>> from storm.expr import compile, Column, Table
    >>> print compile(Column('ROWNUM'))
    ROWNUM
    >>> print compile(Column('ROWNUM', Table('sometable')))
    sometable.ROWNUM

If it is getting escaped, then it is probably due to something else in the Oracle backend.

[Jason Baker (jbaker)]

I removed it from the word list that the backend is keeping internally because it would be escaped with that code:

    >>> compile.add_reserved_words(['rownum'])
    >>> compile(Column('ROWNUM'))
    '"ROWNUM"'
    >>> compile(Column('ROWNUM', Table('sometable')))
    'sometable."ROWNUM"'

(note that the function I'm using for compiling SQLTokens works pretty much the same as the built-in function)

The problem is that, strictly speaking, ROWNUM is a reserved word in Oracle[1]. It's a pseudo-column that exists on every table. So I can do this:

    CREATE TABLE some_table (foo INT);

    SELECT ROWNUM FROM some_table;

But not this:

    CREATE TABLE some_table(foo INT, ROWNUM INT);

    SELECT ROWNUM FROM some_table;

...unless I escape it:

    CREATE TABLE some_table(foo INT, "ROWNUM" INT);

    SELECT "ROWNUM" FROM some_table;

    SELECT ROWNUM FROM some_table;

While typing this out, it hit me that ROWNUM and "ROWNUM" are really two different column names, so perhaps it's better just to have an Oracle-specific PseudoColumn expression type that won't escape reserved words. That way, Column('ROWNUM') gets escaped, and I can use PseudoColumn('ROWNUM') internally without having it escaped.

[1] The list is here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/ap_keywd.htm#i690190