Oracle function-based index reflection fails

[Former user]

Ubuntu, python 2.5.2, sqlalchemy 0.6.x-dev, cx_Oracle 5.0.2, Oracle client 11g.

stromnov@localhost:~$ cat test.py 
# -*- coding: utf-8 -*-
import sqlalchemy

print sqlalchemy.__version__

login, passwd, db = ('login', 'passwd', 'db')
engine = sqlalchemy.create_engine("oracle://%s:%s@%s" % (login, passwd, db), convert_unicode=True, optimize_limits=True, assert_unicode=True)
metadata = sqlalchemy.MetaData(engine, reflect=True)



stromnov@localhost:~$ python2.5 test.py 
0.6beta1
Traceback (most recent call last):
  File "test.py", line 8, in <module>
    metadata = sqlalchemy.MetaData(engine, reflect=True)
  File "/usr/lib/python2.5/site-packages/sqlalchemy/schema.py", line 1622, in __init__
  File "/usr/lib/python2.5/site-packages/sqlalchemy/schema.py", line 1747, in reflect
  File "/usr/lib/python2.5/site-packages/sqlalchemy/schema.py", line 202, in __new__
  File "/usr/lib/python2.5/site-packages/sqlalchemy/schema.py", line 250, in _init
  File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py", line 1450, in reflecttable
  File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/default.py", line 122, in reflecttable
  File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/reflection.py", line 332, in reflecttable
  File "/usr/lib/python2.5/site-packages/sqlalchemy/schema.py", line 202, in __new__
  File "/usr/lib/python2.5/site-packages/sqlalchemy/schema.py", line 248, in _init
  File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py", line 1161, in reflecttable
  File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py", line 1450, in reflecttable
  File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/default.py", line 122, in reflecttable
  File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/reflection.py", line 360, in reflecttable
  File "/usr/lib/python2.5/site-packages/sqlalchemy/util.py", line 666, in __getitem__
KeyError: u'sys_nc00021$'

This code works perfectly with SQLAlchemy 0.5.x branch:

stromnov@localhost:~$ python2.5 test.py 
0.5.5

[Mike Bayer]

hi -

you'll have to put some more effort into describing your issue. Need to know:

1. the full, exact schema of your oracle database, including all tables, synonyms, owners, etc.

2. exact code to reproduce the problem.

All reflection tests pass with oracle:

z-eeks-Computer-3:sqlalchemy classic$ nosetests -v test.engine.test_reflection --dburi oracle://scott:tiger@172.16.248.131/xe
test.engine.test_reflection.ComponentReflectionTest.test_get_columns ... ok
test.engine.test_reflection.ComponentReflectionTest.test_get_columns_with_schema ... ok
test.engine.test_reflection.ComponentReflectionTest.test_get_foreign_keys ... ok
test.engine.test_reflection.ComponentReflectionTest.test_get_foreign_keys_with_schema ... ok
test.engine.test_reflection.ComponentReflectionTest.test_get_indexes ... ok
test.engine.test_reflection.ComponentReflectionTest.test_get_indexes_with_schema ... ok
test.engine.test_reflection.ComponentReflectionTest.test_get_primary_keys ... ok
test.engine.test_reflection.ComponentReflectionTest.test_get_primary_keys_with_schema ... ok
test.engine.test_reflection.ComponentReflectionTest.test_get_schema_names ... ok
test.engine.test_reflection.ComponentReflectionTest.test_get_table_names ... ok
test.engine.test_reflection.ComponentReflectionTest.test_get_table_names_with_schema ... ok
test.engine.test_reflection.ComponentReflectionTest.test_get_table_oid ... ok
test.engine.test_reflection.ComponentReflectionTest.test_get_table_oid_with_schema ... ok
test.engine.test_reflection.ComponentReflectionTest.test_get_view_columns ... ok
test.engine.test_reflection.ComponentReflectionTest.test_get_view_columns_with_schema ... ok
test.engine.test_reflection.ComponentReflectionTest.test_get_view_definition ... ok
test.engine.test_reflection.ComponentReflectionTest.test_get_view_definition_with_schema ... ok
test.engine.test_reflection.ComponentReflectionTest.test_get_view_names ... ok
test.engine.test_reflection.ComponentReflectionTest.test_get_view_names_with_schema ... ok
test.engine.test_reflection.CreateDropTest.test_createdrop ... ok
test.engine.test_reflection.CreateDropTest.test_sorter ... ok
test.engine.test_reflection.CreateDropTest.test_tablenames ... ok
test.engine.test_reflection.CreateDropTest.testcheckfirst ... ok
test.engine.test_reflection.HasSequenceTest.test_hassequence ... ok
test.engine.test_reflection.ReflectionTest.test_autoincrement_col ... ok
test.engine.test_reflection.ReflectionTest.test_basic_override ... ok
test.engine.test_reflection.ReflectionTest.test_basic_reflection ... ok
test.engine.test_reflection.ReflectionTest.test_composite_fk ... ok
test.engine.test_reflection.ReflectionTest.test_composite_pks ... ok
test.engine.test_reflection.ReflectionTest.test_fk_error ... ok
test.engine.test_reflection.ReflectionTest.test_include_columns ... ok
test.engine.test_reflection.ReflectionTest.test_include_columns_indexes ... ok
test.engine.test_reflection.ReflectionTest.test_index_reflection ... ok
test.engine.test_reflection.ReflectionTest.test_nonreflected_fk_raises ... ok
test.engine.test_reflection.ReflectionTest.test_override_existing_fk ... ok
test.engine.test_reflection.ReflectionTest.test_override_keys ... ok
test.engine.test_reflection.ReflectionTest.test_override_nonexistent_fk ... ok
test.engine.test_reflection.ReflectionTest.test_override_pkfk ... ok
test.engine.test_reflection.ReflectionTest.test_pks_not_uniques ... ok
test.engine.test_reflection.ReflectionTest.test_reflect_all ... ok
test.engine.test_reflection.ReflectionTest.test_reserved ... 'test_reserved' unsupported on DB implementation 'oracle+cx_oracle': FIXME: unknown, confirm not fails_on
ok
test.engine.test_reflection.ReflectionTest.test_two_foreign_keys ... ok
test.engine.test_reflection.ReflectionTest.test_unknown_types ... ok
test.engine.test_reflection.ReflectionTest.test_use_existing ... ok
test.engine.test_reflection.SchemaManipulationTest.test_append_constraint_unique ... ok
test.engine.test_reflection.SchemaTest.test_explicit_default_schema ... ok
test.engine.test_reflection.SchemaTest.test_iteration ... ok
test.engine.test_reflection.UnicodeReflectionTest.test_basic ... ok

----------------------------------------------------------------------
Ran 48 tests in 66.712s

OK
z-eeks-Computer-3:sqlalchemy classic$ nosetests -v test.dialect.test_oracle --dburi oracle://scott:tiger@172.16.248.131/xe
test.dialect.test_oracle.BufferedColumnTest.test_fetch ... ok
test.dialect.test_oracle.BufferedColumnTest.test_fetch_single_arraysize ... ok
test.dialect.test_oracle.CompileTest.test_alias_outer_join ... ok
test.dialect.test_oracle.CompileTest.test_limit ... ok
test.dialect.test_oracle.CompileTest.test_long_labels ... ok
test.dialect.test_oracle.CompileTest.test_outer_join ... ok
test.dialect.test_oracle.CompileTest.test_owner ... ok
test.dialect.test_oracle.CompileTest.test_subquery ... ok
test.dialect.test_oracle.ExecuteTest.test_basic ... ok
test.dialect.test_oracle.MultiSchemaTest.test_create_same_names_explicit_schema ... ok
test.dialect.test_oracle.MultiSchemaTest.test_create_same_names_implicit_schema ... ok
test.dialect.test_oracle.MultiSchemaTest.test_reflect_alt_owner_explicit ... ok
test.dialect.test_oracle.MultiSchemaTest.test_reflect_alt_owner_implicit ... ok
test.dialect.test_oracle.MultiSchemaTest.test_reflect_alt_owner_synonyms ... ok
test.dialect.test_oracle.MultiSchemaTest.test_reflect_local_to_remote ... ok
test.dialect.test_oracle.MultiSchemaTest.test_reflect_remote_synonyms ... ok
test.dialect.test_oracle.OutParamTest.test_out_params ... ok
test.dialect.test_oracle.SequenceTest.test_basic ... ok
test.dialect.test_oracle.TypesTest.test_int_not_float ... ok
test.dialect.test_oracle.TypesTest.test_longstring ... ok
test.dialect.test_oracle.TypesTest.test_no_clobs_for_string_params ... ok
test.dialect.test_oracle.TypesTest.test_numerics ... ok
test.dialect.test_oracle.TypesTest.test_raw_lobs ... ok
test.dialect.test_oracle.TypesTest.test_reflect_nvarchar ... ok
test.dialect.test_oracle.TypesTest.test_reflect_raw ... ok
test.dialect.test_oracle.TypesTest.test_type_adapt ... ok
test.dialect.test_oracle.testing_engine ... ok

----------------------------------------------------------------------
Ran 27 tests in 4.940s

[Former user]

Sorry, I can't provide full schema according to NDA.

But I'd figured out, that exception raised after retrieving of column names. This is last log record:

2009-08-23 13:26:39,270 INFO sqlalchemy.engine.base.Engine.0x...992c 
        SELECT a.index_name, a.column_name, b.uniqueness
        FROM ALL_IND_COLUMNS a
        INNER JOIN ALL_INDEXES b
            ON a.index_name = b.index_name
            AND a.table_owner = b.table_owner
            AND a.table_name = b.table_name
        WHERE a.table_name = :table_name
        AND a.table_owner = :schema
        ORDER BY a.index_name, a.column_position
2009-08-23 13:26:39,271 INFO sqlalchemy.engine.base.Engine.0x...992c {'table_name': 'MAILBOX', 'schema': 'MAIL'}

This is results of this query:

In [6](6): engine.execute(query).fetchall()
2009-08-23 14:29:13,005 INFO sqlalchemy.engine.base.Engine.0x...576c 
SELECT a.index_name, a.column_name, b.uniqueness
FROM ALL_IND_COLUMNS a
INNER JOIN ALL_INDEXES b
ON a.index_name = b.index_name
AND a.table_owner = b.table_owner
AND a.table_name = b.table_name
WHERE a.table_name = 'MAILBOX'
AND a.table_owner = 'MAIL'
ORDER BY a.index_name, a.column_position

2009-08-23 14:29:13,005 INFO sqlalchemy.engine.base.Engine.0x...576c {}
Out[6](6): 
['FID', 'NONUNIQUE'),
 ('I_MAILBOX_FLDRCVD', 'SYS_NC00021$', 'NONUNIQUE'),
 ('I_MAILBOX_STID', 'ST_ID', 'NONUNIQUE'),
 ('I_MBOX_THREADID', 'THREAD_ID', 'NONUNIQUE'),
 ('PK_MAILBOX', 'MID', 'UNIQUE')](('I_MAILBOX_FLDRCVD',)

But there is not such field in schema named as 'SYS_NC00021$'.

In [8](8): engine.execute("""SELECT * FROM ALL_IND_COLUMNS WHERE TABLE_NAME = 'MAILBOX'""").fetchall()
Out[8](8): 
['PK_MAILBOX', 'MAIL', 'MAILBOX', 'MID', 1, 22, 0, 'ASC'),
 ('MAIL', 'I_MBOX_THREADID', 'MAIL', 'MAILBOX', 'THREAD_ID', 1, 22, 0, 'ASC'),
 ('MAIL', 'I_MAILBOX_STID', 'MAIL', 'MAILBOX', 'ST_ID', 1, 100, 100, 'ASC'),
 ('MAIL', 'I_MAILBOX_FLDRCVD', 'MAIL', 'MAILBOX', 'FID', 1, 22, 0, 'ASC'),
 ('MAIL', 'I_MAILBOX_FLDRCVD', 'MAIL', 'MAILBOX', 'SYS_NC00021$', 2, 12, 0, 'DESC')](('MAIL',)

I'd found some docs, referring to 'SYS_NC*' columns:

When you create a function based index, oracle creates a hidden column on a base table.
Oracle creates this column as COL# as 0 and NAME as SYS_NC<Oracle Assign Number>$.

[Former user]

Exact code, as I mentioned above is:

stromnov@localhost:~$ cat test.py 
# -*- coding: utf-8 -*-
import sqlalchemy

print sqlalchemy.__version__

login, passwd, db = ('login', 'passwd', 'db')
engine = sqlalchemy.create_engine("oracle://%s:%s@%s" % (login, passwd, db), convert_unicode=True, optimize_limits=True, assert_unicode=True)
metadata = sqlalchemy.MetaData(engine, reflect=True)

[Mike Bayer]

OK can you please provide DDL to create a function-based index in oracle which, upon reflection, recreates that error ? thanks. the issue does not occur in 0.5 because 0.5 does not reflect indexes on Oracle.

[Former user]

Here is snippet, reproducing index reflection:

stromnov@localhost:~$ cat sa_bug_1513.py 
# -*- coding: utf-8 -*-
import sqlalchemy

print sqlalchemy.__version__

login, passwd, db = ('login', 'passwd', 'db')

engine = sqlalchemy.create_engine(
        "oracle://%s:%s@%s" % (login, passwd, db),
        echo = True
    )

query = """
    CREATE TABLE TEST_INDEX_REFLECT
        (
            DATA VARCHAR2(20) NOT NULL,
            CONSTRAINT TEST_INDEX_REFLECT_PK PRIMARY KEY ( DATA ) ENABLE
        )
    """

engine.execute(query)

query = """
    CREATE INDEX DATA_IDX ON TEST_INDEX_REFLECT (UPPER(DATA))
    """

engine.execute(query)

metadata = sqlalchemy.MetaData(engine)

# XXX: This one fails - no such table (bug or feature?)
# metadata.reflect(only=['TEST_INDEX_REFLECT']('TEST_INDEX_REFLECT'))

metadata.reflect(only=['test_index_reflect']('test_index_reflect'))
stromnov@localhost:~$

PS. Note, table created in upper-case, but can be reflected using only lower-case naming.

[Mike Bayer]

Replying to guest:

PS. Note, table created in upper-case, but can be reflected using only lower-case naming.

here's from the API docs for Table:

Names which contain no upper case characters will be treated as case insensitive names, and will not be quoted unless they are a reserved word. Names with any number of upper case characters will be quoted and sent exactly. Note that this behavior applies even for databases which standardize upper case names as case insensitive such as Oracle.

[Mike Bayer]

I added a regexp to skip those names in fb6be4d35906ccc822634a7657ec109b5d8cbb7f. While it might be nicer for get_indexes() to include this information in the result, its not really compatible with the current reflection "columns" collection and I'd rather not allow that list to include non-column names.

[Mike Bayer]

Removing milestone: 0.6.0 (automated comment)