reflecting oracle synonym of synonym over dblink

[dadrake3]

Describe the bug

I get a table not found error when trying to reflect a synonym of a synonym on a dblink. I try reflecting the table as such

self._engine = sql.create_engine(uri, echo='debug')
sql.Table(table_name, self._metadata, schema=self._schema, autoload_with=self._engine, oracle_resolve_synonyms=True)

the queries that it runs are

SELECT
    a_synonyms.synonym_name,
    a_synonyms.table_name,
    a_synonyms.table_owner,
    a_synonyms.db_link
FROM
    all_synonyms a_synonyms
WHERE
    a_synonyms.owner = :owner_1
    AND a_synonyms.synonym_name IN (:synonym_name_1_1);

SELECT
    a_tab_cols.table_name,
    a_tab_cols.column_name,
    a_tab_cols.data_type,
    a_tab_cols.char_length,
    a_tab_cols.data_precision,
    a_tab_cols.data_scale,
    a_tab_cols.nullable,
    a_tab_cols.data_default,
    a_col_comments.comments,
    a_tab_cols.virtual_column,
    a_tab_cols.default_on_null,
    CASE
        WHEN (a_tab_identity_cols.table_name IS NULL) THEN NULL
        ELSE a_tab_identity_cols.generation_type || ',' || a_tab_identity_cols.identity_options
    END AS identity_options
FROM
    all_tab_cols@<DB_LINK> a_tab_cols
LEFT OUTER JOIN all_col_comments@<DB_LINK> a_col_comments ON
    a_tab_cols.table_name = a_col_comments.table_name
    AND a_tab_cols.column_name = a_col_comments.column_name
    AND a_tab_cols.owner = a_col_comments.owner
LEFT OUTER JOIN all_tab_identity_cols@<DB_LINK> a_tab_identity_cols ON
    a_tab_cols.table_name = a_tab_identity_cols.table_name
    AND a_tab_cols.column_name = a_tab_identity_cols.column_name
    AND a_tab_cols.owner = a_tab_identity_cols.owner
WHERE
    a_tab_cols.table_name IN ('<TABLE_NAME>')
    AND a_tab_cols.hidden_column = 'NO'
    AND a_tab_cols.owner = '<OWNER>'
ORDER BY
    a_tab_cols.table_name,
    a_tab_cols.column_id;

The second query returns nothing. It appears that it is taking the table_owner field from the first query and then using that in the second query. However since this is a synonym of a synonym the true table_owner is not the same as the owner of the second synonym, which causes this to return no results.

Optional link from https://docs.sqlalchemy.org which documents the behavior that is expected

No response

SQLAlchemy Version in Use

2.0.25

DBAPI (i.e. the database driver)

oracledb 1.4.2

Database Vendor and Major Version

Oracle 19.0.0.0.0,

Python Version

3.11.7

Operating system

OSX

To Reproduce

import sqlalchemy as sql

table_name = ...
schema = ...
metadata = sql.MetaData()

engine = sql.create_engine(uri, echo='debug')
sql.Table(table_name, metadata, schema=schema, autoload_with=engine, oracle_resolve_synonyms=True)

Error

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "<string>", line 2, in __new__
  File "/Users/<USER>/miniconda3/envs/<CONDA ENV>/lib/python3.11/site-packages/sqlalchemy/util/deprecations.py", line 281, in warned
    return fn(*args, **kwargs)  # type: ignore[no-any-return]
           ^^^^^^^^^^^^^^^^^^^
  File "/Users/<USER>/miniconda3/envs/<CONDA ENV>/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 436, in __new__
    return cls._new(*args, **kw)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/Users/<USER>/miniconda3/envs/<CONDA ENV>/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 490, in _new
    with util.safe_reraise():
  File "/Users/<USER>/miniconda3/envs/<CONDA ENV>/lib/python3.11/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/Users/<USER>/miniconda3/envs/<CONDA ENV>/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 486, in _new
    table.__init__(name, metadata, *args, _no_init=False, **kw)
  File "/Users/<USER>/miniconda3/envs/<CONDA ENV>/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 866, in __init__
    self._autoload(
  File "/Users/<USER>/miniconda3/envs/<CONDA ENV>/lib/python3.11/site-packages/sqlalchemy/sql/schema.py", line 898, in _autoload
    conn_insp.reflect_table(
  File "/Users/<USER>/miniconda3/envs/<CONDA ENV>/lib/python3.11/site-packages/sqlalchemy/engine/reflection.py", line 1538, in reflect_table
    raise exc.NoSuchTableError(table_name)
sqlalchemy.exc.NoSuchTableError: <TABLE NAME>

Additional context

No response

[CaselIT]

Hi

I've yet to relook at the code, but I'm not really sure how to solve this. Using a recursive query to find the actual synonym seems a bit too heavy handed.

@zzzeek do you have suggestions on your part?

@dadrake3 Is the issue blocking of can you work around it by avoiding the double synonym?

[dadrake3]

@CaselIT Yes I was able to work around it by just accessing the base synonym directly once I determined that its a nested synonym.

However, the errors I was getting weren't intuitive, just "table or view does not exist", so I thought Id post about it here

[CaselIT]

ok, thanks for reporting it.

I think that maybe just mentioning it in the docs about synonym in oracle is enough here. @zzzeek what do you think?

[CaselIT]

Talked with mike about this. The idea is to have a better error when a table not found error is raised while reflecting synonyms to check if it's a nested one. So not supporting this use case, but it should at least be easier to debug what's up.

The documentation can also be updated