ROWNUM and ROWID not considered reserved words

Bug #393945 reported by Jason Baker
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Storm
Confirmed
Undecided
Unassigned

Bug Description

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.

Jason Baker (jbaker)
Changed in storm:
status: New → Confirmed
Revision history for this message
James Henstridge (jamesh) wrote :

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.

Revision history for this message
Gustavo Niemeyer (niemeyer) wrote :

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.

Revision history for this message
Jason Baker (jbaker) wrote :

@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.

Revision history for this message
James Henstridge (jamesh) wrote :

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.

Revision history for this message
Jason Baker (jbaker) wrote :

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

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.