Issue 1:
Describe the bug

I have a query which worked in sqlalchemy version 1.3.9, and after upgrading to 1.4.4 It started throwing exception of "Missing property _is_subquery". the query purpose is to count how many shared indexes are between 2 tables.

Expected behavior

return a number.

Code to Reproduce

attached a code I pieced from my unit testing:

table_a = Table(...)
table_b = select(
[col for col in table_a.c if col.name != 'index'] + [(func.row_number().over()).label('index')]
)
index_a = table_a.c['index']
index_b = table_b.c['index']
count = select([func.count()])\
.where(index_a.in_(select([index_b])))\
.execute().scalar()

I've tried adding to the subquery .subquery() or .alias('something') but it didn't help

Error

../tests.py:59: in run
count = select([func.count()]).where(
<string>:2: in execute
???
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/util/deprecations.py:390: in warned
return fn(*args, **kwargs)
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/base.py:932: in execute
return e._execute_clauseelement(
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/engine/base.py:3064: in _execute_clauseelement
return connection._execute_clauseelement(
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/engine/base.py:1381: in _execute_clauseelement
compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py:535: in _compile_w_cache
compiled_sql = self._compiler(
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py:568: in _compiler
return dialect.statement_compiler(dialect, self, **kw)
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py:766: in __init__
Compiled.__init__(self, dialect, statement, **kwargs)
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py:455: in __init__
self.string = self.process(self.statement, **compile_kwargs)
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py:490: in process
return obj._compiler_dispatch(self, **kwargs)
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py:81: in _compiler_dispatch
return meth(self, **kw)
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py:2964: in visit_select
compile_state = select_stmt._compile_state_factory(
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/base.py:492: in create_for_statement
return klass.create_for_statement(statement, compiler, **kw)
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/base.py:490: in create_for_statement
return cls(statement, compiler, **kw)
../../../.virtualenvs/myvirtualenv/lib/python3.8/site-packages/sqlalchemy/sql/selectable.py:4065: in __init__
self.froms = self._get_froms(statement)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <sqlalchemy.sql.selectable.SelectState object at 0x7fb64a4c2680>
statement = <sqlalchemy.sql.selectable.Select object at 0x7fb62a881400>

def _get_froms(self, statement):
seen = set()
froms = []

for item in itertools.chain(
itertools.chain.from_iterable(
[element._from_objects for element in statement._raw_columns]
),
itertools.chain.from_iterable(
[
element._from_objects
for element in statement._where_criteria
]
),
self.from_clauses,
):
> if item._is_subquery and item.element is statement:
E AttributeError: 'Select' object has no attribute '_is_subquery'

Versions.

OS: Mac BigSur

Python: 3.8.6

SQLAlchemy: 1.4.4

Database: PrestoDB

DBAPI:

Thanks

Issue 2:
I use a .in_(select([...])) construction: something like select(scan.xxx).where(scan.id.in_(select(port.scan.id.where(...)))) (the exact code is here).

this code used to work (1.3.23) and no longer works (1.4.4). I would like to understand whether my code was wrong in the first place or if the API has changed, and how I should code that.

Using git bisect, a9b6205 seems to be the "guilty" commit.

With the current SQLAchemy code, I get the following stacktrace:

Traceback (most recent call last):
File "<string>", line 1, in <module>
File "/VENV/lib/python3.9/site-packages/ivre/db/sql/__init__.py", line 1497, in count
return self.db.execute(
File "<string>", line 2, in execute
File "/VENV/lib/python3.9/site-packages/sqlalchemy/util/deprecations.py", line 390, in warned
return fn(*args, **kwargs)
File "/VENV/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3009, in execute
return connection.execute(statement, *multiparams, **params)
File "/VENV/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1200, in execute
return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
File "/VENV/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 313, in _execute_on_connection
return connection._execute_clauseelement(
File "/VENV/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1381, in _execute_clauseelement
compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
File "/VENV/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 533, in _compile_w_cache
compiled_sql = self._compiler(
File "/VENV/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 566, in _compiler
return dialect.statement_compiler(dialect, self, **kw)
File "/VENV/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 766, in __init__
Compiled.__init__(self, dialect, statement, **kwargs)
File "/VENV/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 455, in __init__
self.string = self.process(self.statement, **compile_kwargs)
File "/VENV/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 490, in process
return obj._compiler_dispatch(self, **kwargs)
File "/VENV/lib/python3.9/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
return meth(self, **kw)
File "/VENV/lib/python3.9/site-packages/sqlalchemy/sql/compiler.py", line 2964, in visit_select
compile_state = select_stmt._compile_state_factory(
File "/VENV/lib/python3.9/site-packages/sqlalchemy/sql/base.py", line 492, in create_for_statement
return klass.create_for_statement(statement, compiler, **kw)
File "/VENV/lib/python3.9/site-packages/sqlalchemy/orm/context.py", line 603, in create_for_statement
SelectState.__init__(self, self.statement, compiler, **kw)
File "/VENV/lib/python3.9/site-packages/sqlalchemy/sql/selectable.py", line 4065, in __init__
self.froms = self._get_froms(statement)
File "/VENV/lib/python3.9/site-packages/sqlalchemy/sql/selectable.py", line 4145, in _get_froms
if item._is_subquery and item.element is statement:
AttributeError: 'Select' object has no attribute '_is_subquery'

Thanks in advance!