Issue 1:
Discussed in #9525

still figuring out what this is. Oracle has to use literal render, so this is not really an oracle thing, it's something w literal render

from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import func
from sqlalchemy import Identity
from sqlalchemy import Integer
from sqlalchemy import select
from sqlalchemy import String
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm import foreign
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session

Base = declarative_base()

class Model(Base):
__tablename__ = "model"
id = Column(Integer, Identity(), primary_key=True)
name = Column(String(50))

class Item(Base):
__tablename__ = "item"

id = Column(Integer, Identity(), primary_key=True)
model_name = Column(String(50))

actual_model = relationship(
"Model",
viewonly=True,
primaryjoin=(
"Model.name == func.coalesce("
" foreign(Item.model_name), "
' select(Config.default_model_name).filter(Config.name == "default").limit(1).scalar_subquery()'
")"
),
)

class Config(Base):
__tablename__ = "config"

id = Column(Integer, Identity(), primary_key=True)
name = Column(String(50))
default_model_name = Column(String(50))

# e = create_engine("sqlite://", echo=True)
e = create_engine("oracle://scott:tiger@oracle18c", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)

with Session(e) as sess:
sess.add_all(
[
Config(name="default", default_model_name="model1"),
Item(model_name="model1"),
Model(name="model1"),
]
)
sess.commit()

with Session(e) as sess:

# works fine
stmt = select(
func.coalesce(
foreign(Item.model_name),
select(Config.default_model_name)
.filter(Config.name == "default")
.limit(1)
.scalar_subquery(),
)
)
from sqlalchemy.sql.util import _deep_annotate
stmt = _deep_annotate(stmt, {"foo": "bar"})
sess.execute(stmt)

# ORM version fails
i1 = sess.scalars(select(Item)).one()
print(i1.actual_model)

Traceback (most recent call last):
File "/home/classic/dev/sqlalchemy/test3.py", line 82, in <module>
print(i1.actual_model)
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py", line 557, in __get__
return self.impl.get(state, dict_) # type: ignore[no-any-return]
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py", line 1070, in get
value = self._fire_loader_callables(state, key, passive)
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py", line 1105, in _fire_loader_callables
return self.callable_(state, passive)
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/strategies.py", line 972, in _load_for_state
return self._emit_lazyload(
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/strategies.py", line 1135, in _emit_lazyload
result = session.execute(
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/session.py", line 2229, in execute
return self._execute_internal(
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/session.py", line 2124, in _execute_internal
result: Result[Any] = compile_state_cls.orm_execute_statement(
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/orm/context.py", line 253, in orm_execute_statement
result = conn.execute(
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1414, in execute
return meth(
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/elements.py", line 486, in _execute_on_connection
return connection._execute_clauseelement(
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/engine/base.py", line 1630, in _execute_clauseelement
compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/elements.py", line 652, in _compile_w_cache
compiled_sql = self._compiler(
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/elements.py", line 291, in _compiler
return dialect.statement_compiler(dialect, self, **kw)
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/dialects/oracle/base.py", line 820, in __init__
super().__init__(*args, **kwargs)
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/compiler.py", line 1269, in __init__
Compiled.__init__(self, dialect, statement, **kwargs)
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/compiler.py", line 710, in __init__
self.string = self.process(self.statement, **compile_kwargs)
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/compiler.py", line 755, in process
return obj._compiler_dispatch(self, **kwargs)
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/visitors.py", line 143, in _compiler_dispatch
return meth(self, **kw) # type: ignore # noqa: E501
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/compiler.py", line 4551, in visit_select
text = self._compose_select_body(
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/compiler.py", line 4713, in _compose_select_body
t = self._generate_delimited_and_list(
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/compiler.py", line 2528, in _generate_delimited_and_list
return clauses[0]._compiler_dispatch(self, **kw)
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/annotation.py", line 351, in _compiler_dispatch
return self.__element.__class__._compiler_dispatch(
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/visitors.py", line 143, in _compiler_dispatch
return meth(self, **kw) # type: ignore # noqa: E501
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/compiler.py", line 3143, in visit_binary
return self._generate_generic_binary(
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/compiler.py", line 3199, in _generate_generic_binary
+ binary.right._compiler_dispatch(
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/annotation.py", line 351, in _compiler_dispatch
return self.__element.__class__._compiler_dispatch(
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/visitors.py", line 143, in _compiler_dispatch
return meth(self, **kw) # type: ignore # noqa: E501
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/dialects/oracle/base.py", line 859, in visit_function
text = super().visit_function(func, **kw)
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/compiler.py", line 2711, in visit_function
) % {"expr": self.function_argspec(func, **kwargs)}
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/dialects/oracle/base.py", line 854, in function_argspec
return compiler.SQLCompiler.function_argspec(self, fn, **kw)
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/compiler.py", line 2727, in function_argspec
return func.clause_expr._compiler_dispatch(self, **kwargs)
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/annotation.py", line 351, in _compiler_dispatch
return self.__element.__class__._compiler_dispatch(
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/visitors.py", line 143, in _compiler_dispatch
return meth(self, **kw) # type: ignore # noqa: E501
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/compiler.py", line 2179, in visit_grouping
return "(" + grouping.element._compiler_dispatch(self, **kwargs) + ")"
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/annotation.py", line 351, in _compiler_dispatch
return self.__element.__class__._compiler_dispatch(
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/visitors.py", line 143, in _compiler_dispatch
return meth(self, **kw) # type: ignore # noqa: E501
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/compiler.py", line 2547, in visit_clauselist
return self._generate_delimited_list(clauselist.clauses, sep, **kw)
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/compiler.py", line 2513, in _generate_delimited_list
return separator.join(
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/compiler.py", line 2513, in <genexpr>
return separator.join(
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/compiler.py", line 2515, in <genexpr>
for s in (c._compiler_dispatch(self, **kw) for c in elements)
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/annotation.py", line 351, in _compiler_dispatch
return self.__element.__class__._compiler_dispatch(
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/visitors.py", line 143, in _compiler_dispatch
return meth(self, **kw) # type: ignore # noqa: E501
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/compiler.py", line 2179, in visit_grouping
return "(" + grouping.element._compiler_dispatch(self, **kwargs) + ")"
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/visitors.py", line 143, in _compiler_dispatch
return meth(self, **kw) # type: ignore # noqa: E501
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/compiler.py", line 4551, in visit_select
text = self._compose_select_body(
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/compiler.py", line 4737, in _compose_select_body
text += self._row_limit_clause(select, **kwargs)
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/dialects/oracle/base.py", line 1031, in _row_limit_clause
return self.fetch_clause(
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/compiler.py", line 4931, in fetch_clause
fetch_clause = fetch_clause.render_literal_execute()
File "/home/classic/dev/sqlalchemy/lib/sqlalchemy/sql/elements.py", line 2060, in render_literal_execute
return self.__class__(
TypeError: Annotated.__new__() got an unexpected keyword argument 'type_'

Issue 2:
Migrated issue, originally created by Anonymous

Is this not something that could be enabled?