diff options
| -rw-r--r-- | doc/build/changelog/migration_14.rst | 33 | ||||
| -rw-r--r-- | doc/build/changelog/unreleased_14/4895.rst | 13 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/sqlite/base.py | 12 | ||||
| -rw-r--r-- | lib/sqlalchemy/dialects/sqlite/pysqlite.py | 45 | ||||
| -rw-r--r-- | lib/sqlalchemy/engine/default.py | 4 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/compiler.py | 158 | ||||
| -rw-r--r-- | test/dialect/test_sqlite.py | 11 | ||||
| -rw-r--r-- | test/sql/test_join_rewriting.py | 838 |
8 files changed, 77 insertions, 1037 deletions
diff --git a/doc/build/changelog/migration_14.rst b/doc/build/changelog/migration_14.rst index bd8eca437..15b6bcafe 100644 --- a/doc/build/changelog/migration_14.rst +++ b/doc/build/changelog/migration_14.rst @@ -782,3 +782,36 @@ runs operations on a new transaction. The "test harness" pattern described at :ref:`session_external_transaction` is the common place for this to occur. The new behavior is described in the errors page at :ref:`error_8s2a`. + + +Dialect Changes +=============== + +.. _change_4895: + +Removed "join rewriting" logic from SQLite dialect; updated imports +------------------------------------------------------------------- + +Dropped support for right-nested join rewriting to support old SQLite +versions prior to 3.7.16, released in 2013. It is not expected that +any modern Python versions rely upon this limitation. + +The behavior was first introduced in 0.9 and was part of the larger change of +allowing for right nested joins as described at :ref:`feature_joins_09`. +However the SQLite workaround produced many regressions in the 2013-2014 +period due to its complexity. In 2016, the dialect was modified so that the +join rewriting logic would only occur for SQLite verisons prior to 3.7.16 after +bisection was used to identify where SQLite fixed its support for this +construct, and no further issues were reported against the behavior (even +though some bugs were found internally). It is now anticipated that there +are little to no Python builds for Python 2.7 or 3.4 and above (the supported +Python versions) which would include a SQLite version prior to 3.7.17, and +the behavior is only necessary only in more complex ORM joining scenarios. +A warning is now emitted if the installed SQLite version is older than +3.7.16. + +In related changes, the module imports for SQLite no longer attempt to +import the "pysqlite2" driver on Python 3 as this driver does not exist +on Python 3; a very old warning for old pysqlite2 versions is also dropped. + +:ticket:`4895` diff --git a/doc/build/changelog/unreleased_14/4895.rst b/doc/build/changelog/unreleased_14/4895.rst new file mode 100644 index 000000000..873b11fa9 --- /dev/null +++ b/doc/build/changelog/unreleased_14/4895.rst @@ -0,0 +1,13 @@ +.. change:: + :tags: change, sqlite + :tickets: 4895 + + Dropped support for right-nested join rewriting to support old SQLite + versions prior to 3.7.16, released in 2013. It is expected that + all modern Python versions among those now supported should all include + much newer versions of SQLite. + + .. seealso:: + + :ref:`change_4895` + diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py index defb64ec0..1bb7bd4fc 100644 --- a/lib/sqlalchemy/dialects/sqlite/base.py +++ b/lib/sqlalchemy/dialects/sqlite/base.py @@ -1467,9 +1467,15 @@ class SQLiteDialect(default.DefaultDialect): self.native_datetime = native_datetime if self.dbapi is not None: - self.supports_right_nested_joins = ( - self.dbapi.sqlite_version_info >= (3, 7, 16) - ) + if self.dbapi.sqlite_version_info < (3, 7, 16): + util.warn( + "SQLite version %s is older than 3.7.16, and will not " + "support right nested joins, as are sometimes used in " + "more complex ORM scenarios. SQLAlchemy 1.4 and above " + "no longer tries to rewrite these joins." + % (self.dbapi.sqlite_version_info,) + ) + self._broken_dotted_colnames = self.dbapi.sqlite_version_info < ( 3, 10, diff --git a/lib/sqlalchemy/dialects/sqlite/pysqlite.py b/lib/sqlalchemy/dialects/sqlite/pysqlite.py index 4eca7ae31..89254eef1 100644 --- a/lib/sqlalchemy/dialects/sqlite/pysqlite.py +++ b/lib/sqlalchemy/dialects/sqlite/pysqlite.py @@ -18,19 +18,8 @@ r""" Driver ------ -When using Python 2.5 and above, the built in ``sqlite3`` driver is -already installed and no additional installation is needed. Otherwise, -the ``pysqlite2`` driver needs to be present. This is the same driver as -``sqlite3``, just with a different name. - -The ``pysqlite2`` driver will be loaded first, and if not found, ``sqlite3`` -is loaded. This allows an explicitly installed pysqlite driver to take -precedence over the built in one. As with all dialects, a specific -DBAPI module may be provided to :func:`~sqlalchemy.create_engine()` to control -this explicitly:: - - from sqlite3 import dbapi2 as sqlite - e = create_engine('sqlite+pysqlite:///file.db', module=sqlite) +The ``sqlite3`` Python DBAPI is standard on all modern Python versions; +for cPython and Pypy, no additional installation is necessary. Connect Strings @@ -379,30 +368,18 @@ class SQLiteDialect_pysqlite(SQLiteDialect): driver = "pysqlite" - def __init__(self, uri=False, **kwargs): - SQLiteDialect.__init__(self, **kwargs) - - if self.dbapi is not None: - sqlite_ver = self.dbapi.version_info - if sqlite_ver < (2, 1, 3): - util.warn( - ( - "The installed version of pysqlite2 (%s) is out-dated " - "and will cause errors in some cases. Version 2.1.3 " - "or greater is recommended." - ) - % ".".join([str(subver) for subver in sqlite_ver]) - ) - @classmethod def dbapi(cls): - try: - from pysqlite2 import dbapi2 as sqlite - except ImportError: + if util.py2k: try: - from sqlite3 import dbapi2 as sqlite # try 2.5+ stdlib name. - except ImportError as e: - raise e + from pysqlite2 import dbapi2 as sqlite + except ImportError: + try: + from sqlite3 import dbapi2 as sqlite + except ImportError as e: + raise e + else: + from sqlite3 import dbapi2 as sqlite return sqlite @classmethod diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py index f66f06415..79b8622d5 100644 --- a/lib/sqlalchemy/engine/default.py +++ b/lib/sqlalchemy/engine/default.py @@ -65,7 +65,6 @@ class DefaultDialect(interfaces.Dialect): postfetch_lastrowid = True implicit_returning = False - supports_right_nested_joins = True cte_follows_insert = False supports_native_enum = False @@ -201,7 +200,6 @@ class DefaultDialect(interfaces.Dialect): paramstyle=None, dbapi=None, implicit_returning=None, - supports_right_nested_joins=None, case_sensitive=True, supports_native_boolean=None, empty_in_strategy="static", @@ -232,8 +230,6 @@ class DefaultDialect(interfaces.Dialect): self.positional = self.paramstyle in ("qmark", "format", "numeric") self.identifier_preparer = self.preparer(self) self.type_compiler = self.type_compiler(self) - if supports_right_nested_joins is not None: - self.supports_right_nested_joins = supports_right_nested_joins if supports_native_boolean is not None: self.supports_native_boolean = supports_native_boolean self.case_sensitive = case_sensitive diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 1381e734c..320c7b782 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -2049,136 +2049,6 @@ class SQLCompiler(Compiled): def get_statement_hint_text(self, hint_texts): return " ".join(hint_texts) - def _transform_select_for_nested_joins(self, select): - """Rewrite any "a JOIN (b JOIN c)" expression as - "a JOIN (select * from b JOIN c) AS anon", to support - databases that can't parse a parenthesized join correctly - (i.e. sqlite < 3.7.16). - - """ - cloned = {} - column_translate = [{}] - created = set() - - def visit(element, **kw): - if element in column_translate[-1]: - return column_translate[-1][element] - - elif element in cloned: - return cloned[element] - - newelem = cloned[element] = element._clone() - if ( - newelem._is_from_clause - and newelem._is_join - and isinstance(newelem.right, selectable.FromGrouping) - ): - - newelem._reset_exported() - newelem.left = visit(newelem.left, **kw) - - right = visit(newelem.right, **kw) - - selectable_ = selectable.Select( - [right.element], use_labels=True - ).alias() - created.add(selectable_) - created.update(selectable_.c) - - for c in selectable_.c: - c._key_label = c.key - c._label = c.name - - translate_dict = dict( - zip(newelem.right.element.c, selectable_.c) - ) - - # translating from both the old and the new - # because different select() structures will lead us - # to traverse differently - translate_dict[right.element.left] = selectable_ - translate_dict[right.element.right] = selectable_ - translate_dict[newelem.right.element.left] = selectable_ - translate_dict[newelem.right.element.right] = selectable_ - - # propagate translations that we've gained - # from nested visit(newelem.right) outwards - # to the enclosing select here. this happens - # only when we have more than one level of right - # join nesting, i.e. "a JOIN (b JOIN (c JOIN d))" - for k, v in list(column_translate[-1].items()): - if v in translate_dict: - # remarkably, no current ORM tests (May 2013) - # hit this condition, only test_join_rewriting - # does. - column_translate[-1][k] = translate_dict[v] - - column_translate[-1].update(translate_dict) - - newelem.right = selectable_ - - newelem.onclause = visit(newelem.onclause, **kw) - - elif newelem._is_from_container: - # if we hit an Alias, CompoundSelect or ScalarSelect, put a - # marker in the stack. - kw["transform_clue"] = "select_container" - newelem._copy_internals(clone=visit, **kw) - elif newelem._is_returns_rows and newelem._is_select_statement: - barrier_select = ( - kw.get("transform_clue", None) == "select_container" - ) - # if we're still descended from an - # Alias/CompoundSelect/ScalarSelect, we're - # in a FROM clause, so start with a new translate collection - if barrier_select: - column_translate.append({}) - kw["transform_clue"] = "inside_select" - if not newelem._is_select_container: - froms = newelem.froms - newelem._raw_columns = list(newelem.selected_columns) - newelem._from_obj.update(froms) - newelem._reset_memoizations() - newelem._copy_internals(clone=visit, **kw) - if barrier_select: - del column_translate[-1] - else: - newelem._copy_internals(clone=visit, **kw) - - return newelem - - return visit(select) - - def _transform_result_map_for_nested_joins( - self, select, transformed_select - ): - self._result_columns[:] = [ - result_rec - if col is tcol - else ( - result_rec[0], - name, - tuple([col if obj is tcol else obj for obj in result_rec[2]]), - result_rec[3], - ) - for result_rec, (name, col), (tname, tcol) in zip( - self._result_columns, - select._columns_plus_names, - transformed_select._columns_plus_names, - ) - ] - - # TODO: it's not anticipated that we need to correct anon_map - # however if we do, this is what it looks like: - # for (name, col), (tname, tcol) in zip( - # select._columns_plus_names, - # transformed_select._columns_plus_names, - # ): - # if isinstance(name, elements._anonymous_label) and name != tname: - # m1 = re.match(r"^%\((\d+ .+?)\)s$", name) - # m2 = re.match(r"^%\((\d+ .+?)\)s$", tname) - # self.anon_map[m1.group(1)] = self.anon_map[m2.group(1)] - _default_stack_entry = util.immutabledict( [("correlate_froms", frozenset()), ("asfrom_froms", frozenset())] ) @@ -2214,32 +2084,11 @@ class SQLCompiler(Compiled): asfrom=False, fromhints=None, compound_index=0, - nested_join_translation=False, select_wraps_for=None, lateral=False, **kwargs ): - needs_nested_translation = ( - select.use_labels - and not nested_join_translation - and not self.stack - and not self.dialect.supports_right_nested_joins - ) - - if needs_nested_translation: - transformed_select = self._transform_select_for_nested_joins( - select - ) - text = self.visit_select( - transformed_select, - asfrom=asfrom, - fromhints=fromhints, - compound_index=compound_index, - nested_join_translation=True, - **kwargs - ) - toplevel = not self.stack entry = self._default_stack_entry if toplevel else self.stack[-1] @@ -2258,13 +2107,6 @@ class SQLCompiler(Compiled): if not populate_result_map and "add_to_result_map" in kwargs: del kwargs["add_to_result_map"] - if needs_nested_translation: - if populate_result_map: - self._transform_result_map_for_nested_joins( - select, transformed_select - ) - return text - froms = self._setup_select_stack(select, entry, asfrom, lateral) column_clause_args = kwargs.copy() diff --git a/test/dialect/test_sqlite.py b/test/dialect/test_sqlite.py index 23d22d6ff..7d9c75175 100644 --- a/test/dialect/test_sqlite.py +++ b/test/dialect/test_sqlite.py @@ -592,6 +592,17 @@ class DialectTest(fixtures.TestBase, AssertsExecutionResults): __only_on__ = "sqlite" + def test_3_7_16_warning(self): + with expect_warnings( + r"SQLite version \(3, 2, 8\) is older than 3.7.16, and " + "will not support right nested joins" + ): + sqlite.dialect( + dbapi=mock.Mock( + version_info=(2, 6, 0), sqlite_version_info=(3, 2, 8) + ) + ) + def test_extra_reserved_words(self): """Tests reserved words in identifiers. diff --git a/test/sql/test_join_rewriting.py b/test/sql/test_join_rewriting.py deleted file mode 100644 index e713f5d73..000000000 --- a/test/sql/test_join_rewriting.py +++ /dev/null @@ -1,838 +0,0 @@ -"""These tests are all about the "join rewriting" feature built -to support SQLite's lack of right-nested joins. SQLite as of -version 3.7.16 no longer has this limitation. - -""" -from sqlalchemy import Column -from sqlalchemy import exists -from sqlalchemy import ForeignKey -from sqlalchemy import Integer -from sqlalchemy import literal_column -from sqlalchemy import MetaData -from sqlalchemy import select -from sqlalchemy import Table -from sqlalchemy import testing -from sqlalchemy import union -from sqlalchemy import util -from sqlalchemy.engine import default -from sqlalchemy.sql import elements -from sqlalchemy.testing import AssertsCompiledSQL -from sqlalchemy.testing import eq_ -from sqlalchemy.testing import fixtures - -m = MetaData() - - -a = Table("a", m, Column("id", Integer, primary_key=True)) - -b = Table( - "b", - m, - Column("id", Integer, primary_key=True), - Column("a_id", Integer, ForeignKey("a.id")), -) - -b_a = Table("b_a", m, Column("id", Integer, primary_key=True)) - -b1 = Table( - "b1", - m, - Column("id", Integer, primary_key=True), - Column("a_id", Integer, ForeignKey("a.id")), -) - -b2 = Table( - "b2", - m, - Column("id", Integer, primary_key=True), - Column("a_id", Integer, ForeignKey("a.id")), -) - -a_to_b = Table( - "a_to_b", - m, - Column("a_id", Integer, ForeignKey("a.id")), - Column("b_id", Integer, ForeignKey("b.id")), -) - -c = Table( - "c", - m, - Column("id", Integer, primary_key=True), - Column("b_id", Integer, ForeignKey("b.id")), -) - -d = Table( - "d", - m, - Column("id", Integer, primary_key=True), - Column("c_id", Integer, ForeignKey("c.id")), -) - -e = Table("e", m, Column("id", Integer, primary_key=True)) - -f = Table( - "f", - m, - Column("id", Integer, primary_key=True), - Column("a_id", ForeignKey("a.id")), -) - -b_key = Table("b_key", m, Column("id", Integer, primary_key=True, key="bid")) - -a_to_b_key = Table( - "a_to_b_key", - m, - Column("aid", Integer, ForeignKey("a.id")), - Column("bid", Integer, ForeignKey("b_key.bid")), -) - - -class _JoinRewriteTestBase(AssertsCompiledSQL): - def _test(self, s, assert_): - self.assert_compile(s, assert_) - - compiled = s.compile(dialect=self.__dialect__) - - for rec, (name, col) in zip( - compiled._result_columns, s._columns_plus_names - ): - assert col in set(rec[2]) - if ( - not isinstance(name, elements._anonymous_label) - and name is not None - ): - eq_(rec[1], name) - - _a_bkeyselect_bkey = "" - - def test_a_bkeyselect_bkey(self): - assoc = a_to_b_key.select().alias() - j1 = assoc.join(b_key) - j2 = a.join(j1) - - s = select([a, b_key], use_labels=True).select_from(j2) - self._test(s, self._a_bkeyselect_bkey) - - def test_a_bc(self): - j1 = b.join(c) - j2 = a.join(j1) - - # TODO: if we remove 'b' or 'c', shouldn't we get just - # the subset of cols from anon_1 ? - - # TODO: do this test also with individual cols, things change - # lots based on how you go with this - - s = ( - select([a, b, c], use_labels=True) - .select_from(j2) - .where(b.c.id == 2) - .where(c.c.id == 3) - .order_by(a.c.id, b.c.id, c.c.id) - ) - - self._test(s, self._a_bc) - - def test_a_bc_preserve_dupes(self): - j1 = b.join(c) - j2 = a.join(j1) - - s = ( - select( - [a.c.id, b.c.id, b.c.a_id, c, b.c.a_id, c.c.b_id], - use_labels=True, - ) - .select_from(j2) - .where(b.c.id == 2) - .where(c.c.id == 3) - .order_by(a.c.id, b.c.id, c.c.id) - ) - - self._test(s, self._a_bc_wdupes) - - def test_a_bc_preserve_dupes_anon_map(self): - j1 = b.join(c) - j2 = a.join(j1) - - s = ( - select( - [a.c.id, b.c.id, b.c.a_id, c, b.c.a_id, c.c.b_id], - use_labels=True, - ) - .select_from(j2) - .where(b.c.id == 2) - .where(c.c.id == 3) - ) - - # the anon_map needs to be preserved after the transform - # as the labels are going to be referred to outside of the query - subq = s.subquery() - s2 = ( - select([literal_column("1")]) - .select_from(subq) - .where(subq.c[5] == subq.c[6]) - ) - - self._test(s2, self._a_bc_wdupes_anon_map) - - def test_a_bkeyassoc(self): - j1 = b_key.join(a_to_b_key) - j2 = a.join(j1) - - s = select([a, b_key.c.bid], use_labels=True).select_from(j2) - - self._test(s, self._a_bkeyassoc) - - def test_a_bkeyassoc_aliased(self): - bkey_alias = b_key.alias() - a_to_b_key_alias = a_to_b_key.alias() - - j1 = bkey_alias.join(a_to_b_key_alias) - j2 = a.join(j1) - - s = select([a, bkey_alias.c.bid], use_labels=True).select_from(j2) - - self._test(s, self._a_bkeyassoc_aliased) - - def test_a__b_dc(self): - j1 = c.join(d) - j2 = b.join(j1) - j3 = a.join(j2) - - s = ( - select([a, b, c, d], use_labels=True) - .select_from(j3) - .where(b.c.id == 2) - .where(c.c.id == 3) - .where(d.c.id == 4) - .order_by(a.c.id, b.c.id, c.c.id, d.c.id) - ) - - self._test(s, self._a__b_dc) - - def test_a_bc_comma_a1_selbc(self): - # test here we're emulating is - # test.orm.inheritance.test_polymorphic_rel: - # PolymorphicJoinsTest.test_multi_join - j1 = b.join(c) - j2 = b.join(c).select(use_labels=True).alias() - j3 = a.join(j1) - a_a = a.alias() - j4 = a_a.join(j2) - - s = ( - select([a, a_a, b, c, j2], use_labels=True) - .select_from(j3) - .select_from(j4) - .order_by(j2.c.b_id) - ) - - self._test(s, self._a_bc_comma_a1_selbc) - - def test_a_atobalias_balias_c_w_exists(self): - a_to_b_alias = a_to_b.alias() - b_alias = b.alias() - - j1 = a_to_b_alias.join(b_alias) - j2 = a.outerjoin(j1, a.c.id == a_to_b_alias.c.a_id) - - # TODO: if we put straight a_to_b_alias here, - # it fails to alias the columns clause. - s = select( - [ - a, - a_to_b_alias.c.a_id, - a_to_b_alias.c.b_id, - b_alias.c.id, - b_alias.c.a_id, - exists() - .select_from(c) - .where(c.c.b_id == b_alias.c.id) - .label(None), - ], - use_labels=True, - ).select_from(j2) - - self._test(s, self._a_atobalias_balias_c_w_exists) - - def test_a_atobalias_balias(self): - a_to_b_alias = a_to_b.alias() - b_alias = b.alias() - - j1 = a_to_b_alias.join(b_alias) - j2 = a.outerjoin(j1, a.c.id == a_to_b_alias.c.a_id) - - s = select([a, a_to_b_alias, b_alias], use_labels=True).select_from(j2) - - self._test(s, self._a_atobalias_balias) - - def test_b_ab1_union_b_ab2(self): - j1 = a.join(b1) - j2 = a.join(b2) - - b_j1 = b.join(j1) - b_j2 = b.join(j2) - - s = ( - union( - select([b_j1], use_labels=True), - select([b_j2], use_labels=True), - ) - .subquery() - .select(use_labels=True) - ) - - self._test(s, self._b_ab1_union_c_ab2) - - def test_b_a_id_double_overlap_annotated(self): - # test issue #3057 - # this involves annotations so try to loop those in. - j1 = b.join(b_a, b.c.id == b_a.c.id) - annot = [ - b.c.id._annotate({}), - b.c.a_id._annotate({}), - b_a.c.id._annotate({}), - ] - - s = select(annot).select_from(j1).apply_labels().alias() - - s = select(list(s.c)).apply_labels() - - self._test(s, self._b_a_id_double_overlap_annotated) - - def test_f_b1a_where_in_b2a(self): - # test issue #3130 - b1a = a.join(b1) - b2a = a.join(b2) - subq = select([b2.c.id]).select_from(b2a) - s = select([f]).select_from(f.join(b1a)).where(b1.c.id.in_(subq)) - - s = s.apply_labels() - self._test(s, self._f_b1a_where_in_b2a) - - def test_anon_scalar_subqueries(self): - s1 = select([1]).scalar_subquery() - s2 = select([2]).scalar_subquery() - - s = select([s1, s2]).apply_labels() - self._test(s, self._anon_scalar_subqueries) - - -class JoinRewriteTest(_JoinRewriteTestBase, fixtures.TestBase): - - """test rendering of each join with right-nested rewritten as - aliased SELECT statements..""" - - @util.classproperty - def __dialect__(cls): - dialect = default.DefaultDialect() - dialect.supports_right_nested_joins = False - return dialect - - _a__b_dc = ( - "SELECT a.id AS a_id, anon_1.b_id AS b_id, " - "anon_1.b_a_id AS b_a_id, anon_1.c_id AS c_id, " - "anon_1.c_b_id AS c_b_id, anon_1.d_id AS d_id, " - "anon_1.d_c_id AS d_c_id " - "FROM a JOIN (SELECT b.id AS b_id, b.a_id AS b_a_id, " - "anon_2.c_id AS c_id, anon_2.c_b_id AS c_b_id, " - "anon_2.d_id AS d_id, anon_2.d_c_id AS d_c_id " - "FROM b JOIN (SELECT c.id AS c_id, c.b_id AS c_b_id, " - "d.id AS d_id, d.c_id AS d_c_id " - "FROM c JOIN d ON c.id = d.c_id) AS anon_2 " - "ON b.id = anon_2.c_b_id) AS anon_1 ON a.id = anon_1.b_a_id " - "WHERE anon_1.b_id = :id_1 AND anon_1.c_id = :id_2 AND " - "anon_1.d_id = :id_3 " - "ORDER BY a.id, anon_1.b_id, anon_1.c_id, anon_1.d_id" - ) - - _a_bc = ( - "SELECT a.id AS a_id, anon_1.b_id AS b_id, " - "anon_1.b_a_id AS b_a_id, anon_1.c_id AS c_id, " - "anon_1.c_b_id AS c_b_id FROM a JOIN " - "(SELECT b.id AS b_id, b.a_id AS b_a_id, " - "c.id AS c_id, c.b_id AS c_b_id " - "FROM b JOIN c ON b.id = c.b_id) AS anon_1 " - "ON a.id = anon_1.b_a_id " - "WHERE anon_1.b_id = :id_1 AND anon_1.c_id = :id_2 " - "ORDER BY a.id, anon_1.b_id, anon_1.c_id" - ) - - _a_bc_wdupes = ( - "SELECT a.id AS a_id, anon_1.b_id AS b_id, anon_1.b_a_id AS b_a_id, " - "anon_1.c_id AS c_id, anon_1.c_b_id AS c_b_id, " - "anon_1.b_a_id AS b_a_id__1, anon_1.c_b_id AS c_b_id__1 " - "FROM a JOIN " - "(SELECT b.id AS b_id, b.a_id AS b_a_id, c.id AS c_id, " - "c.b_id AS c_b_id " - "FROM b JOIN c ON b.id = c.b_id) AS anon_1 ON a.id = anon_1.b_a_id " - "WHERE anon_1.b_id = :id_1 AND anon_1.c_id = :id_2 " - "ORDER BY a.id, anon_1.b_id, anon_1.c_id" - ) - - _a_bc_wdupes_anon_map = ( - "SELECT 1 FROM (SELECT a.id AS a_id, b.id AS b_id, b.a_id AS b_a_id, " - "c.id AS c_id, c.b_id AS c_b_id, b.a_id AS b_a_id__1, " - "c.b_id AS c_b_id__1 FROM a JOIN (b JOIN c ON b.id = c.b_id) " - "ON a.id = b.a_id WHERE b.id = :id_1 AND c.id = :id_2) AS anon_1 " - "WHERE anon_1.b_a_id = anon_1.c_b_id" - ) - - _a_bc_comma_a1_selbc = ( - "SELECT a.id AS a_id, a_1.id AS a_1_id, anon_1.b_id AS b_id, " - "anon_1.b_a_id AS b_a_id, anon_1.c_id AS c_id, " - "anon_1.c_b_id AS c_b_id, anon_2.b_id AS anon_2_b_id, " - "anon_2.b_a_id AS anon_2_b_a_id, anon_2.c_id AS anon_2_c_id, " - "anon_2.c_b_id AS anon_2_c_b_id FROM a " - "JOIN (SELECT b.id AS b_id, b.a_id AS b_a_id, c.id AS c_id, " - "c.b_id AS c_b_id FROM b JOIN c ON b.id = c.b_id) AS anon_1 " - "ON a.id = anon_1.b_a_id, " - "a AS a_1 JOIN " - "(SELECT b.id AS b_id, b.a_id AS b_a_id, " - "c.id AS c_id, c.b_id AS c_b_id " - "FROM b JOIN c ON b.id = c.b_id) AS anon_2 " - "ON a_1.id = anon_2.b_a_id ORDER BY anon_2.b_id" - ) - - _a_bkeyassoc = ( - "SELECT a.id AS a_id, anon_1.b_key_id AS b_key_id " - "FROM a JOIN " - "(SELECT b_key.id AS b_key_id, a_to_b_key.aid AS a_to_b_key_aid, " - "a_to_b_key.bid AS a_to_b_key_bid FROM b_key " - "JOIN a_to_b_key ON b_key.id = a_to_b_key.bid) AS anon_1 " - "ON a.id = anon_1.a_to_b_key_aid" - ) - - _a_bkeyassoc_aliased = ( - "SELECT a.id AS a_id, anon_1.b_key_1_id AS b_key_1_id " - "FROM a JOIN (SELECT b_key_1.id AS b_key_1_id, " - "a_to_b_key_1.aid AS a_to_b_key_1_aid, " - "a_to_b_key_1.bid AS a_to_b_key_1_bid FROM b_key AS b_key_1 " - "JOIN a_to_b_key AS a_to_b_key_1 ON b_key_1.id = a_to_b_key_1.bid) AS " - "anon_1 ON a.id = anon_1.a_to_b_key_1_aid" - ) - - _a_bkeyselect_bkey = ( - "SELECT a.id AS a_id, anon_1.b_key_id AS b_key_id " - "FROM a JOIN (SELECT anon_2.aid AS anon_2_aid, " - "anon_2.bid AS anon_2_bid, " - "b_key.id AS b_key_id " - "FROM (SELECT a_to_b_key.aid AS aid, a_to_b_key.bid AS bid " - "FROM a_to_b_key) AS anon_2 " - "JOIN b_key ON b_key.id = anon_2.bid) AS anon_1 " - "ON a.id = anon_1.anon_2_aid" - ) - - _a_atobalias_balias_c_w_exists = ( - "SELECT a.id AS a_id, " - "anon_1.a_to_b_1_a_id AS a_to_b_1_a_id, " - "anon_1.a_to_b_1_b_id AS a_to_b_1_b_id, " - "anon_1.b_1_id AS b_1_id, anon_1.b_1_a_id AS b_1_a_id, " - "EXISTS (SELECT * FROM c WHERE c.b_id = anon_1.b_1_id) AS anon_2 " - "FROM a LEFT OUTER JOIN (SELECT a_to_b_1.a_id AS a_to_b_1_a_id, " - "a_to_b_1.b_id AS a_to_b_1_b_id, b_1.id AS b_1_id, " - "b_1.a_id AS b_1_a_id " - "FROM a_to_b AS a_to_b_1 " - "JOIN b AS b_1 ON b_1.id = a_to_b_1.b_id) AS anon_1 " - "ON a.id = anon_1.a_to_b_1_a_id" - ) - - _a_atobalias_balias = ( - "SELECT a.id AS a_id, anon_1.a_to_b_1_a_id AS a_to_b_1_a_id, " - "anon_1.a_to_b_1_b_id AS a_to_b_1_b_id, anon_1.b_1_id AS b_1_id, " - "anon_1.b_1_a_id AS b_1_a_id FROM a LEFT OUTER JOIN " - "(SELECT a_to_b_1.a_id AS a_to_b_1_a_id, " - "a_to_b_1.b_id AS a_to_b_1_b_id, " - "b_1.id AS b_1_id, b_1.a_id AS b_1_a_id FROM a_to_b AS a_to_b_1 " - "JOIN b AS b_1 ON b_1.id = a_to_b_1.b_id) AS anon_1 " - "ON a.id = anon_1.a_to_b_1_a_id" - ) - - _b_ab1_union_c_ab2 = ( - "SELECT anon_1.b_id AS anon_1_b_id, anon_1.b_a_id AS anon_1_b_a_id, " - "anon_1.a_id AS anon_1_a_id, anon_1.b1_id AS anon_1_b1_id, " - "anon_1.b1_a_id AS anon_1_b1_a_id FROM " - "(SELECT b.id AS b_id, b.a_id AS b_a_id, anon_2.a_id AS a_id, " - "anon_2.b1_id AS b1_id, anon_2.b1_a_id AS b1_a_id " - "FROM b JOIN (SELECT a.id AS a_id, b1.id AS b1_id, b1.a_id AS b1_a_id " - "FROM a JOIN b1 ON a.id = b1.a_id) AS anon_2 ON anon_2.a_id = b.a_id " - "UNION " - "SELECT b.id AS b_id, b.a_id AS b_a_id, anon_3.a_id AS a_id, " - "anon_3.b2_id AS b2_id, anon_3.b2_a_id AS b2_a_id " - "FROM b JOIN (SELECT a.id AS a_id, b2.id AS b2_id, b2.a_id AS b2_a_id " - "FROM a JOIN b2 ON a.id = b2.a_id) AS anon_3 ON anon_3.a_id = b.a_id) " - "AS anon_1" - ) - - _b_a_id_double_overlap_annotated = ( - "SELECT anon_1.b_id AS anon_1_b_id, anon_1.b_a_id AS anon_1_b_a_id, " - "anon_1.b_a_id_1 AS anon_1_b_a_id_1 " - "FROM (SELECT b.id AS b_id, b.a_id AS b_a_id, b_a.id AS b_a_id_1 " - "FROM b JOIN b_a ON b.id = b_a.id) AS anon_1" - ) - - _f_b1a_where_in_b2a = ( - "SELECT f.id AS f_id, f.a_id AS f_a_id " - "FROM f JOIN (SELECT a.id AS a_id, b1.id AS b1_id, b1.a_id AS b1_a_id " - "FROM a JOIN b1 ON a.id = b1.a_id) AS anon_1 ON anon_1.a_id = f.a_id " - "WHERE anon_1.b1_id IN (SELECT b2.id " - "FROM a JOIN b2 ON a.id = b2.a_id)" - ) - - _anon_scalar_subqueries = ( - "SELECT (SELECT 1) AS anon_1, (SELECT 2) AS anon_2" - ) - - -class JoinPlainTest(_JoinRewriteTestBase, fixtures.TestBase): - - """test rendering of each join with normal nesting.""" - - @util.classproperty - def __dialect__(cls): - dialect = default.DefaultDialect() - return dialect - - _a_bkeyselect_bkey = ( - "SELECT a.id AS a_id, b_key.id AS b_key_id FROM a JOIN " - "((SELECT a_to_b_key.aid AS aid, a_to_b_key.bid AS bid " - "FROM a_to_b_key) AS anon_1 JOIN b_key ON b_key.id = anon_1.bid) " - "ON a.id = anon_1.aid" - ) - - _a__b_dc = ( - "SELECT a.id AS a_id, b.id AS b_id, " - "b.a_id AS b_a_id, c.id AS c_id, " - "c.b_id AS c_b_id, d.id AS d_id, " - "d.c_id AS d_c_id " - "FROM a JOIN (b JOIN (c JOIN d ON c.id = d.c_id) " - "ON b.id = c.b_id) ON a.id = b.a_id " - "WHERE b.id = :id_1 AND c.id = :id_2 AND " - "d.id = :id_3 " - "ORDER BY a.id, b.id, c.id, d.id" - ) - - _a_bc = ( - "SELECT a.id AS a_id, b.id AS b_id, " - "b.a_id AS b_a_id, c.id AS c_id, " - "c.b_id AS c_b_id FROM a JOIN " - "(b JOIN c ON b.id = c.b_id) " - "ON a.id = b.a_id " - "WHERE b.id = :id_1 AND c.id = :id_2 " - "ORDER BY a.id, b.id, c.id" - ) - - _a_bc_wdupes = ( - "SELECT a.id AS a_id, b.id AS b_id, b.a_id AS b_a_id, c.id AS c_id, " - "c.b_id AS c_b_id, b.a_id AS b_a_id__1, c.b_id AS c_b_id__1 " - "FROM a JOIN " - "(b JOIN c ON b.id = c.b_id) " - "ON a.id = b.a_id " - "WHERE b.id = :id_1 AND c.id = :id_2 " - "ORDER BY a.id, b.id, c.id" - ) - - _a_bc_wdupes_anon_map = ( - "SELECT 1 FROM (SELECT a.id AS a_id, b.id AS b_id, b.a_id AS b_a_id, " - "c.id AS c_id, c.b_id AS c_b_id, b.a_id AS b_a_id__1, " - "c.b_id AS c_b_id__1 " - "FROM a JOIN (b JOIN c ON b.id = c.b_id) ON a.id = b.a_id " - "WHERE b.id = :id_1 AND c.id = :id_2) AS anon_1 " - "WHERE anon_1.b_a_id = anon_1.c_b_id" - ) - - _a_bc_comma_a1_selbc = ( - "SELECT a.id AS a_id, a_1.id AS a_1_id, b.id AS b_id, " - "b.a_id AS b_a_id, c.id AS c_id, " - "c.b_id AS c_b_id, anon_1.b_id AS anon_1_b_id, " - "anon_1.b_a_id AS anon_1_b_a_id, anon_1.c_id AS anon_1_c_id, " - "anon_1.c_b_id AS anon_1_c_b_id FROM a " - "JOIN (b JOIN c ON b.id = c.b_id) " - "ON a.id = b.a_id, " - "a AS a_1 JOIN " - "(SELECT b.id AS b_id, b.a_id AS b_a_id, " - "c.id AS c_id, c.b_id AS c_b_id " - "FROM b JOIN c ON b.id = c.b_id) AS anon_1 " - "ON a_1.id = anon_1.b_a_id ORDER BY anon_1.b_id" - ) - - _a_bkeyassoc = ( - "SELECT a.id AS a_id, b_key.id AS b_key_id " - "FROM a JOIN " - "(b_key JOIN a_to_b_key ON b_key.id = a_to_b_key.bid) " - "ON a.id = a_to_b_key.aid" - ) - - _a_bkeyassoc_aliased = ( - "SELECT a.id AS a_id, b_key_1.id AS b_key_1_id FROM a " - "JOIN (b_key AS b_key_1 JOIN a_to_b_key AS a_to_b_key_1 " - "ON b_key_1.id = a_to_b_key_1.bid) ON a.id = a_to_b_key_1.aid" - ) - - _a_atobalias_balias_c_w_exists = ( - "SELECT a.id AS a_id, a_to_b_1.a_id AS a_to_b_1_a_id, " - "a_to_b_1.b_id AS a_to_b_1_b_id, b_1.id AS b_1_id, " - "b_1.a_id AS b_1_a_id, " - "EXISTS (SELECT * FROM c WHERE c.b_id = b_1.id) AS anon_1 " - "FROM a LEFT OUTER JOIN " - "(a_to_b AS a_to_b_1 JOIN b AS b_1 ON b_1.id = a_to_b_1.b_id) " - "ON a.id = a_to_b_1.a_id" - ) - - _a_atobalias_balias = ( - "SELECT a.id AS a_id, a_to_b_1.a_id AS a_to_b_1_a_id, " - "a_to_b_1.b_id AS a_to_b_1_b_id, b_1.id AS b_1_id, " - "b_1.a_id AS b_1_a_id " - "FROM a LEFT OUTER JOIN (a_to_b AS a_to_b_1 " - "JOIN b AS b_1 ON b_1.id = a_to_b_1.b_id) ON a.id = a_to_b_1.a_id" - ) - - _b_ab1_union_c_ab2 = ( - "SELECT anon_1.b_id AS anon_1_b_id, anon_1.b_a_id AS anon_1_b_a_id, " - "anon_1.a_id AS anon_1_a_id, anon_1.b1_id AS anon_1_b1_id, " - "anon_1.b1_a_id AS anon_1_b1_a_id FROM " - "(SELECT b.id AS b_id, b.a_id AS b_a_id, a.id AS a_id, " - "b1.id AS b1_id, " - "b1.a_id AS b1_a_id FROM b " - "JOIN (a JOIN b1 ON a.id = b1.a_id) ON a.id = b.a_id " - "UNION " - "SELECT b.id AS b_id, b.a_id AS b_a_id, a.id AS a_id, b2.id AS b2_id, " - "b2.a_id AS b2_a_id FROM b " - "JOIN (a JOIN b2 ON a.id = b2.a_id) ON a.id = b.a_id) AS anon_1" - ) - - _b_a_id_double_overlap_annotated = ( - "SELECT anon_1.b_id AS anon_1_b_id, anon_1.b_a_id AS anon_1_b_a_id, " - "anon_1.b_a_id_1 AS anon_1_b_a_id_1 FROM " - "(SELECT b.id AS b_id, b.a_id AS b_a_id, b_a.id AS b_a_id_1 " - "FROM b JOIN b_a ON b.id = b_a.id) AS anon_1" - ) - - _f_b1a_where_in_b2a = ( - "SELECT f.id AS f_id, f.a_id AS f_a_id " - "FROM f JOIN (a JOIN b1 ON a.id = b1.a_id) ON a.id = f.a_id " - "WHERE b1.id IN (SELECT b2.id " - "FROM a JOIN b2 ON a.id = b2.a_id)" - ) - - _anon_scalar_subqueries = ( - "SELECT (SELECT 1) AS anon_1, (SELECT 2) AS anon_2" - ) - - -class JoinNoUseLabelsTest(_JoinRewriteTestBase, fixtures.TestBase): - @util.classproperty - def __dialect__(cls): - dialect = default.DefaultDialect() - dialect.supports_right_nested_joins = False - return dialect - - def _test(self, s, assert_): - s.use_labels = False - self.assert_compile(s, assert_) - - _a_bkeyselect_bkey = ( - "SELECT a.id, b_key.id FROM a JOIN ((SELECT a_to_b_key.aid AS aid, " - "a_to_b_key.bid AS bid FROM a_to_b_key) AS anon_1 " - "JOIN b_key ON b_key.id = anon_1.bid) ON a.id = anon_1.aid" - ) - - _a__b_dc = ( - "SELECT a.id, b.id, " - "b.a_id, c.id, " - "c.b_id, d.id, " - "d.c_id " - "FROM a JOIN (b JOIN (c JOIN d ON c.id = d.c_id) " - "ON b.id = c.b_id) ON a.id = b.a_id " - "WHERE b.id = :id_1 AND c.id = :id_2 AND " - "d.id = :id_3 " - "ORDER BY a.id, b.id, c.id, d.id" - ) - - _a_bc = ( - "SELECT a.id, b.id, " - "b.a_id, c.id, " - "c.b_id FROM a JOIN " - "(b JOIN c ON b.id = c.b_id) " - "ON a.id = b.a_id " - "WHERE b.id = :id_1 AND c.id = :id_2 " - "ORDER BY a.id, b.id, c.id" - ) - - _a_bc_wdupes = ( - "SELECT a.id, b.id, b.a_id, c.id, c.b_id, b.a_id, c.b_id " - "FROM a JOIN " - "(b JOIN c ON b.id = c.b_id) " - "ON a.id = b.a_id " - "WHERE b.id = :id_1 AND c.id = :id_2 " - "ORDER BY a.id, b.id, c.id" - ) - - _a_bc_wdupes_anon_map = ( - "SELECT 1 FROM (SELECT a.id AS a_id, b.id AS b_id, b.a_id AS b_a_id, " - "c.id AS c_id, c.b_id AS c_b_id, b.a_id AS b_a_id__1, " - "c.b_id AS c_b_id__1 " - "FROM a JOIN (b JOIN c ON b.id = c.b_id) ON a.id = b.a_id " - "WHERE b.id = :id_1 AND c.id = :id_2) AS anon_1 " - "WHERE anon_1.b_a_id = anon_1.c_b_id" - ) - - _a_bc_comma_a1_selbc = ( - "SELECT a.id, a_1.id, b.id, " - "b.a_id, c.id, " - "c.b_id, anon_1.b_id, " - "anon_1.b_a_id, anon_1.c_id, " - "anon_1.c_b_id FROM a " - "JOIN (b JOIN c ON b.id = c.b_id) " - "ON a.id = b.a_id, " - "a AS a_1 JOIN " - "(SELECT b.id AS b_id, b.a_id AS b_a_id, " - "c.id AS c_id, c.b_id AS c_b_id " - "FROM b JOIN c ON b.id = c.b_id) AS anon_1 " - "ON a_1.id = anon_1.b_a_id ORDER BY anon_1.b_id" - ) - - _a_bkeyassoc = ( - "SELECT a.id, b_key.id FROM a JOIN (b_key JOIN a_to_b_key " - "ON b_key.id = a_to_b_key.bid) ON a.id = a_to_b_key.aid" - ) - - _a_bkeyassoc_aliased = ( - "SELECT a.id, b_key_1.id FROM a JOIN (b_key AS b_key_1 " - "JOIN a_to_b_key AS a_to_b_key_1 ON b_key_1.id = a_to_b_key_1.bid) " - "ON a.id = a_to_b_key_1.aid" - ) - - _a_atobalias_balias_c_w_exists = ( - "SELECT a.id, a_to_b_1.a_id, a_to_b_1.b_id, b_1.id, b_1.a_id, " - "EXISTS (SELECT * FROM c WHERE c.b_id = b_1.id) AS anon_1 " - "FROM a LEFT OUTER JOIN " - "(a_to_b AS a_to_b_1 JOIN b AS b_1 ON b_1.id = a_to_b_1.b_id) " - "ON a.id = a_to_b_1.a_id" - ) - - _a_atobalias_balias = ( - "SELECT a.id, a_to_b_1.a_id, a_to_b_1.b_id, b_1.id, b_1.a_id " - "FROM a LEFT OUTER JOIN (a_to_b AS a_to_b_1 " - "JOIN b AS b_1 ON b_1.id = a_to_b_1.b_id) ON a.id = a_to_b_1.a_id" - ) - - _b_ab1_union_c_ab2 = ( - "SELECT anon_1.b_id, anon_1.b_a_id, anon_1.a_id, anon_1.b1_id, " - "anon_1.b1_a_id " - "FROM (SELECT b.id AS b_id, b.a_id AS b_a_id, a.id AS a_id, " - "b1.id AS b1_id, b1.a_id AS b1_a_id " - "FROM b JOIN (a JOIN b1 ON a.id = b1.a_id) ON a.id = b.a_id " - "UNION " - "SELECT b.id AS b_id, b.a_id AS b_a_id, a.id AS a_id, b2.id AS b2_id, " - "b2.a_id AS b2_a_id " - "FROM b JOIN (a JOIN b2 ON a.id = b2.a_id) ON a.id = b.a_id) AS anon_1" - ) - - _b_a_id_double_overlap_annotated = ( - "SELECT anon_1.b_id, anon_1.b_a_id, anon_1.b_a_id_1 FROM " - "(SELECT b.id AS b_id, b.a_id AS b_a_id, b_a.id AS b_a_id_1 " - "FROM b JOIN b_a ON b.id = b_a.id) AS anon_1" - ) - - _f_b1a_where_in_b2a = ( - "SELECT f.id, f.a_id " - "FROM f JOIN (a JOIN b1 ON a.id = b1.a_id) ON a.id = f.a_id " - "WHERE b1.id IN (SELECT b2.id " - "FROM a JOIN b2 ON a.id = b2.a_id)" - ) - - _anon_scalar_subqueries = ( - "SELECT (SELECT 1) AS anon_1, (SELECT 2) AS anon_2" - ) - - -class JoinExecTest(_JoinRewriteTestBase, fixtures.TestBase): - - """invoke the SQL on the current backend to ensure compatibility""" - - __backend__ = True - - _a_bc = ( - _a_bc_wdupes - ) = ( - _a_bc_wdupes_anon_map - ) = ( - _a_bc_comma_a1_selbc - ) = ( - _a__b_dc - ) = ( - _a_bkeyassoc - ) = ( - _a_bkeyassoc_aliased - ) = ( - _a_atobalias_balias_c_w_exists - ) = ( - _a_atobalias_balias - ) = ( - _b_ab1_union_c_ab2 - ) = ( - _b_a_id_double_overlap_annotated - ) = _f_b1a_where_in_b2a = _anon_scalar_subqueries = None - - @classmethod - def setup_class(cls): - m.create_all(testing.db) - - @classmethod - def teardown_class(cls): - m.drop_all(testing.db) - - def _test(self, selectable, assert_): - result = testing.db.execute(selectable) - result.close() - for col in selectable.inner_columns: - assert col in result._metadata._keymap - - @testing.skip_if("oracle", "oracle's cranky") - @testing.skip_if( - "mssql", "can't query EXISTS in the columns " "clause w/o subquery" - ) - def test_a_atobalias_balias_c_w_exists(self): - super(JoinExecTest, self).test_a_atobalias_balias_c_w_exists() - - @testing.only_on( - "sqlite", - "non-standard aliasing rules used at the moment, " - "possibly fix this or add another test that uses " - "cross-compatible aliasing", - ) - def test_b_ab1_union_b_ab2(self): - super(JoinExecTest, self).test_b_ab1_union_b_ab2() - - -class DialectFlagTest(fixtures.TestBase, AssertsCompiledSQL): - def test_dialect_flag(self): - d1 = default.DefaultDialect(supports_right_nested_joins=True) - d2 = default.DefaultDialect(supports_right_nested_joins=False) - - j1 = b.join(c) - j2 = a.join(j1) - - s = select([a, b, c], use_labels=True).select_from(j2) - - self.assert_compile( - s, - "SELECT a.id AS a_id, b.id AS b_id, b.a_id AS b_a_id, " - "c.id AS c_id, " - "c.b_id AS c_b_id FROM a JOIN (b JOIN c ON b.id = c.b_id) " - "ON a.id = b.a_id", - dialect=d1, - ) - self.assert_compile( - s, - "SELECT a.id AS a_id, anon_1.b_id AS b_id, " - "anon_1.b_a_id AS b_a_id, " - "anon_1.c_id AS c_id, anon_1.c_b_id AS c_b_id " - "FROM a JOIN (SELECT b.id AS b_id, b.a_id AS b_a_id, " - "c.id AS c_id, " - "c.b_id AS c_b_id FROM b JOIN c ON b.id = c.b_id) AS anon_1 " - "ON a.id = anon_1.b_a_id", - dialect=d2, - ) |