Hi all,
I'll start with an example. Assume A and B are Table objects:
>>> print select([A.id], from_obj=[A], whereclause=(B.field == 123))
SELECT A.id FROM A, B WHERE B.id = 123
As a convenience, sqlalchemy has added B to the FROM clause on the
assumption that I meant to do this.
However, in my experience, this behavior has hidden many bugs. It isn't
a feature we intentionally rely on. Usually, someone forgot a join and
the query sqlalchemy invents is drastically underconstrained. These
bugs sometimes go unnoticed for a long time. I want to raise an
exception when this happens instead of letting the computer take a guess
at what the human meant.
I have modified _get_display_froms from selectable.py to return fewer
FROM entities. So far, this seems to be working. Can you think of any
pitfalls with this change?
I've attached a copy of the patch in case you want to take a look. If
there is interest in merging the change, it would need to be made opt-in
(how is this best done globally--not in a per-query fashion?).
- Dave
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Index: selectable.py
===================================================================
--- selectable.py (revision 137582)
+++ selectable.py (working copy)
@@ -2746,8 +2746,7 @@
GenerativeSelect.__init__(self, **kwargs)
- @property
- def _froms(self):
+ def _froms_impl(self, *from_iterables):
# would love to cache this,
# but there's just enough edge cases, particularly now that
# declarative encourages construction of SQL expressions
@@ -2756,12 +2755,7 @@
seen = set()
translate = self._from_cloned
- for item in itertools.chain(
- _from_objects(*self._raw_columns),
- _from_objects(self._whereclause)
- if self._whereclause is not None else (),
- self._from_obj
- ):
+ for item in itertools.chain(*from_iterables):
if item is self:
raise exc.InvalidRequestError(
"select() construct refers to itself as a FROM")
@@ -2773,6 +2767,30 @@
return froms
+ @property
+ def _froms(self):
+ return self._froms_impl(
+ _from_objects(*self._raw_columns),
+ _from_objects(self._whereclause)
+ if self._whereclause is not None else (),
+ self._from_obj)
+
+ @property
+ def _display_froms(self):
+ if len(self._from_obj) == 0:
+ column_froms = set(_from_objects(*self._raw_columns))
+ if len(column_froms) == 1:
+ # If the caller didn't specify any FROM entities, and
+ # they only seem to be selecting columns from one
+ # table, then it is very likely they expect the one
+ # table to be selected from. We could eliminate this
+ # case (as it goes against the spirit of mandating
+ # explicit FROMs) if we eliminated the various uses of
+ # Session.query(some_column).
+ return self._froms_impl(column_froms)
+ # Only use FROM entities the caller explicitly requested.
+ return self._froms_impl(self._from_obj)
+
def _get_display_froms(self, explicit_correlate_froms=None,
implicit_correlate_froms=None):
"""Return the full list of 'from' clauses to be displayed.
@@ -2783,7 +2801,7 @@
correlating.
"""
- froms = self._froms
+ froms = self._display_froms
toremove = set(itertools.chain(*[
_expand_cloned(f._hide_froms)
