python source code of test

sqlalchemy-master
- .github
  - pull_request_template.md
  - ISSUE_TEMPLATE
    - question.md
    - feature_request.md
    - bug_report.md
  - CONTRIBUTING.md
  - FUNDING.yml
  - SECURITY.md
  - workflows
    - create-wheels.yaml
    - run-on-pr.yaml
    - run-test.yaml
  - CODE_OF_CONDUCT.md
- regen_callcounts.tox.ini
- AUTHORS
- reap_dbs.py
- .coveragerc
- CHANGES
- examples
  - join_conditions
    - cast.py
    - threeway.py
    - __init__.py
  - inheritance
    - joined.py
    - concrete.py
    - single.py
    - __init__.py
  - graphs
    - __init__.py
    - directed_graph.py
  - association
    - dict_of_sets_with_default.py
    - basic_association.py
    - proxied_association.py
    - __init__.py
  - elementtree
    - test3.xml
    - test.xml
    - pickle_type.py
    - test2.xml
    - optimized_al.py
    - __init__.py
    - adjacency_list.py
  - dogpile_caching
    - caching_query.py
    - relationship_caching.py
    - advanced.py
    - local_session_caching.py
    - helloworld.py
    - fixture_data.py
    - model.py
    - __init__.py
    - environment.py
  - adjacency_list
    - __init__.py
    - adjacency_list.py
  - vertical
    - dictlike-polymorphic.py
    - dictlike.py
    - __init__.py
  - nested_sets
    - nested_sets.py
    - __init__.py
  - generic_associations
    - table_per_related.py
    - table_per_association.py
    - __init__.py
    - discriminator_on_association.py
    - generic_fk.py
  - sharding
    - attribute_shard.py
    - __init__.py
  - __init__.py
  - materialized_paths
    - materialized_paths.py
    - __init__.py
  - space_invaders
    - space_invaders.py
    - __init__.py
  - postgis
    - __init__.py
    - postgis.py
  - large_collection
    - large_collection.py
    - __init__.py
  - dynamic_dict
    - __init__.py
    - dynamic_dict.py
  - versioned_history
    - history_meta.py
    - __init__.py
    - test_versioning.py
  - versioned_rows
    - versioned_rows.py
    - versioned_map.py
    - versioned_update_old_row.py
    - __init__.py
    - versioned_rows_w_versionid.py
  - performance
    - single_inserts.py
    - large_resultsets.py
    - bulk_inserts.py
    - short_selects.py
    - __main__.py
    - __init__.py
    - bulk_updates.py
  - custom_attributes
    - active_column_defaults.py
    - __init__.py
    - custom_management.py
    - listen_for_events.py
- .pre-commit-config.yaml
- LICENSE
- test
  - sql
    - test_select.py
    - test_resultset.py
    - test_values.py
    - test_utils.py
    - test_compare.py
    - test_insert_exec.py
    - test_quote.py
    - test_ddlemit.py
    - test_insert.py
    - test_lambdas.py
    - test_external_traversal.py
    - test_metadata.py
    - test_types.py
    - test_sequences.py
    - test_from_linter.py
    - test_lateral.py
    - test_tablesample.py
    - test_query.py
    - test_constraints.py
    - test_selectable.py
    - test_compiler.py
    - test_case_statement.py
    - test_returning.py
    - test_computed.py
    - test_roles.py
    - test_functions.py
    - test_cte.py
    - __init__.py
    - test_type_expressions.py
    - test_text.py
    - test_inspect.py
    - test_delete.py
    - test_deprecations.py
    - test_operators.py
    - test_update.py
    - test_defaults.py
    - test_labels.py
  - binary_data_one.dat
  - orm
    - test_subquery_relations.py
    - inheritance
      - test_basic.py
      - _poly_fixtures.py
      - test_concrete.py
      - test_manytomany.py
      - test_polymorphic_rel.py
      - test_magazine.py
      - test_with_poly.py
      - test_relationship.py
      - test_poly_linked_list.py
      - test_productspec.py
      - test_abc_polymorphic.py
      - test_poly_persistence.py
      - test_single.py
      - __init__.py
      - test_abc_inheritance.py
      - test_selects.py
      - test_poly_loading.py
      - test_assorted_poly.py
    - test_bulk.py
    - test_lockmode.py
    - test_utils.py
    - test_instrumentation.py
    - test_dynamic.py
    - test_compile.py
    - test_collection.py
    - test_loading.py
    - test_manytomany.py
    - test_onetoone.py
    - test_attributes.py
    - test_association.py
    - _fixtures.py
    - test_lambdas.py
    - test_naturalpks.py
    - test_options.py
    - test_events.py
    - test_descriptor.py
    - test_immediate_load.py
    - test_load_on_fks.py
    - test_validators.py
    - test_composites.py
    - test_query.py
    - test_deferred.py
    - test_relationships.py
    - test_mapper.py
    - test_backref_mutations.py
    - test_scoping.py
    - test_joins.py
    - test_selectable.py
    - test_ac_relationships.py
    - test_sync.py
    - test_expire.py
    - test_froms.py
    - test_bind.py
    - test_pickled.py
    - test_rel_fn.py
    - test_transaction.py
    - test_merge.py
    - test_of_type.py
    - test_bundle.py
    - test_session.py
    - test_update_delete.py
    - __init__.py
    - test_hasparent.py
    - test_unitofwork.py
    - test_generative.py
    - test_cascade.py
    - test_selectin_relations.py
    - test_assorted_eager.py
    - test_inspect.py
    - test_deprecations.py
    - test_cycles.py
    - test_core_compilation.py
    - test_evaluator.py
    - test_cache_key.py
    - test_defaults.py
    - test_unitofworkv2.py
    - test_lazy_relations.py
    - test_versioning.py
    - test_default_strategies.py
    - test_eager_relations.py
  - base
    - test_warnings.py
    - test_except.py
    - test_utils.py
    - test_events.py
    - __init__.py
    - test_tutorials.py
    - test_inspect.py
    - test_dependency.py
    - test_result.py
  - binary_data_two.dat
  - profiles.txt
  - aaa_profiling
    - test_resultset.py
    - test_misc.py
    - test_pool.py
    - test_orm.py
    - test_compiler.py
    - test_memusage.py
    - __init__.py
  - engine
    - test_reflection.py
    - test_parseconnect.py
    - test_pool.py
    - test_reconnect.py
    - test_execute.py
    - test_bind.py
    - test_transaction.py
    - test_ddlevents.py
    - __init__.py
    - test_deprecations.py
    - test_processors.py
    - test_logging.py
  - requirements.py
  - ext
    - declarative
      - test_basic.py
      - test_reflection.py
      - test_clsregistry.py
      - test_mixin.py
      - test_inheritance.py
      - __init__.py
      - test_concurrency.py
    - test_associationproxy.py
    - test_compiler.py
    - test_indexable.py
    - test_hybrid.py
    - test_serializer.py
    - test_horizontal_shard.py
    - test_orderinglist.py
    - test_mutable.py
    - test_baked.py
    - __init__.py
    - test_extendedattr.py
    - test_deprecations.py
    - test_automap.py
  - __init__.py
  - perf
    - invalidate_stresstest.py
    - orm2010.py
  - dialect
    - test_firebird.py
    - test_pyodbc.py
    - test_suite.py
    - mssql
      - test_types.py
      - test_reflection.py
      - test_sequence.py
      - test_query.py
      - test_compiler.py
      - test_engine.py
      - __init__.py
    - oracle
      - test_types.py
      - test_reflection.py
      - test_compiler.py
      - test_dialect.py
      - __init__.py
    - mysql
      - test_types.py
      - test_reflection.py
      - test_query.py
      - test_compiler.py
      - test_on_duplicate.py
      - test_dialect.py
      - __init__.py
      - test_for_update.py
      - test_deprecations.py
    - postgresql
      - test_on_conflict.py
      - test_types.py
      - test_reflection.py
      - test_query.py
      - test_compiler.py
      - test_dialect.py
      - __init__.py
    - test_mxodbc.py
    - test_all.py
    - __init__.py
    - test_sybase.py
    - test_deprecations.py
    - test_sqlite.py
  - conftest.py
- README.unittests.rst
- lib
  - sqlalchemy
    - sql
      - elements.py
      - naming.py
      - util.py
      - selectable.py
      - default_comparator.py
      - annotation.py
      - expression.py
      - schema.py
      - events.py
      - functions.py
      - roles.py
      - coercions.py
      - crud.py
      - ddl.py
      - lambdas.py
      - __init__.py
      - sqltypes.py
      - compiler.py
      - traversals.py
      - type_api.py
      - operators.py
      - visitors.py
      - dml.py
      - base.py
    - future
      - engine.py
      - orm
        __init__.py
      - selectable.py
      - __init__.py
    - pool
      - dbapi_proxy.py
      - impl.py
      - events.py
      - __init__.py
      - base.py
    - log.py
    - orm
      - evaluator.py
      - properties.py
      - state.py
      - util.py
      - unitofwork.py
      - interfaces.py
      - identity.py
      - attributes.py
      - persistence.py
      - descriptor_props.py
      - dynamic.py
      - collections.py
      - events.py
      - sync.py
      - strategy_options.py
      - query.py
      - instrumentation.py
      - exc.py
      - strategies.py
      - scoping.py
      - __init__.py
      - mapper.py
      - loading.py
      - relationships.py
      - path_registry.py
      - context.py
      - session.py
      - dependency.py
      - base.py
    - cextension
      - immutabledict.c
      - utils.c
      - processors.c
      - resultproxy.c
    - processors.py
    - schema.py
    - events.py
    - util
      - deprecations.py
      - _collections.py
      - queue.py
      - langhelpers.py
      - topological.py
      - __init__.py
      - compat.py
    - engine
      - util.py
      - interfaces.py
      - default.py
      - row.py
      - mock.py
      - events.py
      - cursor.py
      - strategies.py
      - result.py
      - __init__.py
      - reflection.py
      - create.py
      - url.py
      - base.py
    - dialects
      - sybase
        mxodbc.py
        pysybase.py
        pyodbc.py
        __init__.py
        base.py
      - type_migration_guidelines.txt
      - mssql
        mxodbc.py
        provision.py
        pyodbc.py
        information_schema.py
        pymssql.py
        __init__.py
        base.py
      - sqlite
        pysqlcipher.py
        provision.py
        __init__.py
        pysqlite.py
        base.py
        json.py
      - oracle
        cx_oracle.py
        provision.py
        __init__.py
        base.py
      - mysql
        pymysql.py
        cymysql.py
        oursql.py
        enumerated.py
        provision.py
        pyodbc.py
        mysqlconnector.py
        mysqldb.py
        __init__.py
        reflection.py
        types.py
        dml.py
        base.py
        json.py
      - postgresql
        pypostgresql.py
        psycopg2.py
        ext.py
        provision.py
        pygresql.py
        array.py
        __init__.py
        psycopg2cffi.py
        pg8000.py
        hstore.py
        ranges.py
        dml.py
        base.py
        json.py
      - __init__.py
      - firebird
        fdb.py
        kinterbasdb.py
        __init__.py
        base.py
    - event
      - legacy.py
      - api.py
      - registry.py
      - __init__.py
      - attr.py
      - base.py
    - exc.py
    - ext
      - declarative
        clsregistry.py
        api.py
        __init__.py
        base.py
      - serializer.py
      - baked.py
      - mutable.py
      - indexable.py
      - instrumentation.py
      - hybrid.py
      - associationproxy.py
      - __init__.py
      - automap.py
      - orderinglist.py
      - compiler.py
      - horizontal_shard.py
    - __init__.py
    - inspection.py
    - databases
      - __init__.py
    - types.py
    - connectors
      - mxodbc.py
      - pyodbc.py
      - __init__.py
    - testing
      - util.py
      - entities.py
      - pickleable.py
      - config.py
      - engines.py
      - plugin
        bootstrap.py
        plugin_base.py
        __init__.py
        pytestplugin.py
      - mock.py
      - schema.py
      - provision.py
      - requirements.py
      - assertions.py
      - suite
        test_select.py
        test_ddl.py
        test_insert.py
        test_types.py
        test_reflection.py
        test_sequence.py
        test_rowcount.py
        test_dialect.py
        test_cte.py
        test_unicode_ddl.py
        test_update_delete.py
        __init__.py
        test_deprecations.py
        test_results.py
      - __init__.py
      - fixtures.py
      - warnings.py
      - assertsql.py
      - profiling.py
      - exclusions.py
- README.dialects.rst
- README.rst
- setup.py
- setup.cfg
- .gitreview
- pyproject.toml
- .gitignore
- MANIFEST.in
- doc
  - build
    - orm
      - mapping_api.rst
      - mapped_sql_expr.rst
      - extensions
        baked.rst
        declarative
        inheritance.rst
        table_config.rst
        mixins.rst
        api.rst
        relationships.rst
        index.rst
        basic_use.rst
        horizontal_shard.rst
        hybrid.rst
        mutable.rst
        orderinglist.rst
        associationproxy.rst
        instrumentation.rst
        index.rst
        automap.rst
        indexable.rst
      - cascades.rst
      - mapping_columns.rst
      - inheritance.rst
      - basic_relationships.rst
      - relationship_api.rst
      - exceptions.rst
      - classical.rst
      - join_conditions.rst
      - loading_objects.rst
      - constructors.rst
      - session_api.rst
      - composites.rst
      - session_basics.rst
      - loading_columns.rst
      - versioning.rst
      - tutorial.rst
      - contextual.rst
      - relationships.rst
      - session_transaction.rst
      - examples.rst
      - relationship_persistence.rst
      - session.rst
      - mapper_config.rst
      - session_events.rst
      - nonstandard_mappings.rst
      - inheritance_loading.rst
      - session_state_management.rst
      - index.rst
      - loading.rst
      - backref.rst
      - extending.rst
      - mapped_attributes.rst
      - loading_relationships.rst
      - persistence_techniques.rst
      - scalar_mapping.rst
      - internals.rst
      - self_referential.rst
      - mapping_styles.rst
      - query.rst
      - events.rst
      - collections.rst
    - Makefile
    - faq
      - sessions.rst
      - ormconfiguration.rst
      - sqlexpressions.rst
      - performance.rst
      - metadata_schema.rst
      - connections.rst
      - index.rst
    - copyright.rst
    - errors.rst
    - intro.rst
    - glossary.rst
    - dialects
      - mssql.rst
      - sqlite.rst
      - mysql.rst
      - firebird.rst
      - postgresql.rst
      - index.rst
      - sybase.rst
      - oracle.rst
    - contents.rst
    - changelog
      - changelog_14.rst
      - migration_12.rst
      - migration_11.rst
      - changelog_01.rst
      - migration_05.rst
      - changelog_05.rst
      - changelog_09.rst
      - changelog_07.rst
      - unreleased_11
        README.txt
      - changelog_10.rst
      - unreleased_13
        README.txt
      - migration_14.rst
      - changelog_02.rst
      - changelog_04.rst
      - migration_06.rst
      - migration_10.rst
      - changelog_11.rst
      - README.txt
      - changelog_06.rst
      - migration_13.rst
      - unreleased_14
        5380.rst
        asbool_join.rst
        4696.rst
        4980.rst
        5192.rst
        4737.rst
        4878.rst
        4638.rst
        5054.rst
        1653.rst
        bind_removed_from_compiler.rst
        4632.rst
        5401.rst
        4826.rst
        5122.rst
        5171.rst
        4195.rst
        drop_python34.rst
        removed_depthfirst.rst
        4895.rst
        oracle_limit.rst
        checks_deferred_to_compile.rst
        4617_coercion.rst
        4194.rst
        2056.rst
        4656.rst
        4621.rst
        4868.rst
        5244.rst
        4914.rst
        4753.rst
        4718.rst
        4719.rst
        4976.rst
        README.txt
        4643.rst
        4857.rst
        527.rst
        5084.rst
        4741.rst
        4994.rst
        5315.rst
        4617_scalar.rst
        5004.rst
        4808.rst
        5367.rst
        5134.rst
        4524.rst
        4449.rst
        4519.rst
        4712.rst
        4971.rst
        4617_implicit_subquery.rst
        5131.rst
        4887.rst
        4645.rst
        4662.rst
        4336.rst
        5191.rst
        5094.rst
        result.rst
        4002.rst
        5226.rst
        4235.rst
        4789.rst
        4644.rst
        1763.rst
        5404.rst
        5221.rst
        4649.rst
        5074.rst
        4642.rst
        4829.rst
        psycopg2_27.rst
        4877.rst
        4212.rst
        4755.rst
        orm_update_delete.rst
        5413.rst
        5263.rst
        4639.rst
        4710.rst
        5001.rst
        4836.rst
        5189.rst
      - migration_20.rst
      - migration_08.rst
      - changelog_03.rst
      - migration_09.rst
      - changelog_08.rst
      - index.rst
      - changelog_12.rst
      - migration_07.rst
      - unreleased_12
        README.txt
      - migration_04.rst
      - changelog_13.rst
    - requirements.txt
    - core
      - functions.rst
      - ddl.rst
      - constraints.rst
      - serializer.rst
      - engines_connections.rst
      - exceptions.rst
      - engines.rst
      - api_basics.rst
      - type_api.rst
      - types.rst
      - event.rst
      - dml.rst
      - visitors.rst
      - compiler.rst
      - tutorial.rst
      - reflection.rst
      - inspection.rst
      - custom_types.rst
      - defaults.rst
      - pooling.rst
      - expression_api.rst
      - future.rst
      - type_basics.rst
      - connections.rst
      - selectable.rst
      - metadata.rst
      - index.rst
      - internals.rst
      - sqlelement.rst
      - schema.rst
      - events.rst
    - index.rst
    - conf.py
    - texinputs
      - Makefile
      - sphinx.sty
- tox.ini

from sqlalchemy import ForeignKey
from sqlalchemy import func
from sqlalchemy import Integer
from sqlalchemy import select
from sqlalchemy import String
from sqlalchemy.orm import aliased
from sqlalchemy.orm import Bundle
from sqlalchemy.orm import mapper
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session
from sqlalchemy.sql.elements import ClauseList
from sqlalchemy.testing import AssertsCompiledSQL
from sqlalchemy.testing import eq_
from sqlalchemy.testing import fixtures
from sqlalchemy.testing.schema import Column
from sqlalchemy.testing.schema import Table


class BundleTest(fixtures.MappedTest, AssertsCompiledSQL):
    __dialect__ = "default"

    run_inserts = "once"
    run_setup_mappers = "once"
    run_deletes = None

    @classmethod
    def define_tables(cls, metadata):
        Table(
            "data",
            metadata,
            Column(
                "id", Integer, primary_key=True, test_needs_autoincrement=True
            ),
            Column("d1", String(10)),
            Column("d2", String(10)),
            Column("d3", String(10)),
        )

        Table(
            "other",
            metadata,
            Column(
                "id", Integer, primary_key=True, test_needs_autoincrement=True
            ),
            Column("data_id", ForeignKey("data.id")),
            Column("o1", String(10)),
        )

    @classmethod
    def setup_classes(cls):
        class Data(cls.Basic):
            pass

        class Other(cls.Basic):
            pass

    @classmethod
    def setup_mappers(cls):
        mapper(
            cls.classes.Data,
            cls.tables.data,
            properties={"others": relationship(cls.classes.Other)},
        )
        mapper(cls.classes.Other, cls.tables.other)

    @classmethod
    def insert_data(cls, connection):
        sess = Session(connection)
        sess.add_all(
            [
                cls.classes.Data(
                    d1="d%dd1" % i,
                    d2="d%dd2" % i,
                    d3="d%dd3" % i,
                    others=[
                        cls.classes.Other(o1="d%do%d" % (i, j))
                        for j in range(5)
                    ],
                )
                for i in range(10)
            ]
        )
        sess.commit()

    def test_same_named_col_clauselist(self):
        Data, Other = self.classes("Data", "Other")
        bundle = Bundle("pk", Data.id, Other.id)

        self.assert_compile(ClauseList(Data.id, Other.id), "data.id, other.id")
        self.assert_compile(bundle.__clause_element__(), "data.id, other.id")

    def test_same_named_col_in_orderby(self):
        Data, Other = self.classes("Data", "Other")
        bundle = Bundle("pk", Data.id, Other.id)
        sess = Session()

        self.assert_compile(
            sess.query(Data, Other).order_by(bundle),
            "SELECT data.id AS data_id, data.d1 AS data_d1, "
            "data.d2 AS data_d2, data.d3 AS data_d3, "
            "other.id AS other_id, other.data_id AS other_data_id, "
            "other.o1 AS other_o1 "
            "FROM data, other ORDER BY data.id, other.id",
        )

    def test_same_named_col_in_fetch(self):
        Data, Other = self.classes("Data", "Other")
        bundle = Bundle("pk", Data.id, Other.id)
        sess = Session()

        eq_(
            sess.query(bundle)
            .filter(Data.id == Other.id)
            .filter(Data.id < 3)
            .all(),
            [((1, 1),), ((2, 2),)],
        )

    def test_c_attr(self):
        Data = self.classes.Data

        b1 = Bundle("b1", Data.d1, Data.d2)

        self.assert_compile(
            select([b1.c.d1, b1.c.d2]), "SELECT data.d1, data.d2 FROM data"
        )

    def test_result(self):
        Data = self.classes.Data
        sess = Session()

        b1 = Bundle("b1", Data.d1, Data.d2)

        eq_(
            sess.query(b1).filter(b1.c.d1.between("d3d1", "d5d1")).all(),
            [(("d3d1", "d3d2"),), (("d4d1", "d4d2"),), (("d5d1", "d5d2"),)],
        )

    def test_subclass(self):
        Data = self.classes.Data
        sess = Session()

        class MyBundle(Bundle):
            def create_row_processor(self, query, procs, labels):
                def proc(row):
                    return dict(zip(labels, (proc(row) for proc in procs)))

                return proc

        b1 = MyBundle("b1", Data.d1, Data.d2)

        eq_(
            sess.query(b1).filter(b1.c.d1.between("d3d1", "d5d1")).all(),
            [
                ({"d2": "d3d2", "d1": "d3d1"},),
                ({"d2": "d4d2", "d1": "d4d1"},),
                ({"d2": "d5d2", "d1": "d5d1"},),
            ],
        )

    def test_multi_bundle(self):
        Data = self.classes.Data
        Other = self.classes.Other

        d1 = aliased(Data)

        b1 = Bundle("b1", d1.d1, d1.d2)
        b2 = Bundle("b2", Data.d1, Other.o1)

        sess = Session()

        q = (
            sess.query(b1, b2)
            .join(Data.others)
            .join(d1, d1.id == Data.id)
            .filter(b1.c.d1 == "d3d1")
        )
        eq_(
            q.all(),
            [
                (("d3d1", "d3d2"), ("d3d1", "d3o0")),
                (("d3d1", "d3d2"), ("d3d1", "d3o1")),
                (("d3d1", "d3d2"), ("d3d1", "d3o2")),
                (("d3d1", "d3d2"), ("d3d1", "d3o3")),
                (("d3d1", "d3d2"), ("d3d1", "d3o4")),
            ],
        )

    def test_single_entity(self):
        Data = self.classes.Data
        sess = Session()

        b1 = Bundle("b1", Data.d1, Data.d2, single_entity=True)

        eq_(
            sess.query(b1).filter(b1.c.d1.between("d3d1", "d5d1")).all(),
            [("d3d1", "d3d2"), ("d4d1", "d4d2"), ("d5d1", "d5d2")],
        )

    def test_single_entity_flag_but_multi_entities(self):
        Data = self.classes.Data
        sess = Session()

        b1 = Bundle("b1", Data.d1, Data.d2, single_entity=True)
        b2 = Bundle("b1", Data.d3, single_entity=True)

        eq_(
            sess.query(b1, b2).filter(b1.c.d1.between("d3d1", "d5d1")).all(),
            [
                (("d3d1", "d3d2"), ("d3d3",)),
                (("d4d1", "d4d2"), ("d4d3",)),
                (("d5d1", "d5d2"), ("d5d3",)),
            ],
        )

    def test_bundle_nesting(self):
        Data = self.classes.Data
        sess = Session()

        b1 = Bundle("b1", Data.d1, Bundle("b2", Data.d2, Data.d3))

        eq_(
            sess.query(b1)
            .filter(b1.c.d1.between("d3d1", "d7d1"))
            .filter(b1.c.b2.c.d2.between("d4d2", "d6d2"))
            .all(),
            [
                (("d4d1", ("d4d2", "d4d3")),),
                (("d5d1", ("d5d2", "d5d3")),),
                (("d6d1", ("d6d2", "d6d3")),),
            ],
        )

    def test_bundle_nesting_unions(self):
        Data = self.classes.Data
        sess = Session()

        b1 = Bundle("b1", Data.d1, Bundle("b2", Data.d2, Data.d3))

        q1 = (
            sess.query(b1)
            .filter(b1.c.d1.between("d3d1", "d7d1"))
            .filter(b1.c.b2.c.d2.between("d4d2", "d5d2"))
        )

        q2 = (
            sess.query(b1)
            .filter(b1.c.d1.between("d3d1", "d7d1"))
            .filter(b1.c.b2.c.d2.between("d5d2", "d6d2"))
        )

        eq_(
            q1.union(q2).all(),
            [
                (("d4d1", ("d4d2", "d4d3")),),
                (("d5d1", ("d5d2", "d5d3")),),
                (("d6d1", ("d6d2", "d6d3")),),
            ],
        )

        # naming structure is preserved
        row = q1.union(q2).first()
        eq_(row.b1.d1, "d4d1")
        eq_(row.b1.b2.d2, "d4d2")

    def test_query_count(self):
        Data = self.classes.Data
        b1 = Bundle("b1", Data.d1, Data.d2)
        eq_(Session().query(b1).count(), 10)

    def test_join_relationship(self):
        Data = self.classes.Data

        sess = Session()
        b1 = Bundle("b1", Data.d1, Data.d2)
        q = sess.query(b1).join(Data.others)
        self.assert_compile(
            q,
            "SELECT data.d1 AS data_d1, data.d2 "
            "AS data_d2 FROM data "
            "JOIN other ON data.id = other.data_id",
        )

    def test_join_selectable(self):
        Data = self.classes.Data
        Other = self.classes.Other

        sess = Session()
        b1 = Bundle("b1", Data.d1, Data.d2)
        q = sess.query(b1).join(Other)
        self.assert_compile(
            q,
            "SELECT data.d1 AS data_d1, data.d2 AS data_d2 "
            "FROM data "
            "JOIN other ON data.id = other.data_id",
        )

    def test_joins_from_adapted_entities(self):
        Data = self.classes.Data

        # test for #1853 in terms of bundles
        # specifically this exercises adapt_to_selectable()

        b1 = Bundle("b1", Data.id, Data.d1, Data.d2)

        session = Session()
        first = session.query(b1)
        second = session.query(b1)
        unioned = first.union(second)
        subquery = session.query(Data.id).subquery()
        joined = unioned.outerjoin(subquery, subquery.c.id == Data.id)
        joined = joined.order_by(Data.id, Data.d1, Data.d2)

        self.assert_compile(
            joined,
            "SELECT anon_1.data_id AS anon_1_data_id, "
            "anon_1.data_d1 AS anon_1_data_d1, "
            "anon_1.data_d2 AS anon_1_data_d2 FROM "
            "(SELECT data.id AS data_id, data.d1 AS data_d1, "
            "data.d2 AS data_d2 FROM "
            "data UNION SELECT data.id AS data_id, data.d1 AS data_d1, "
            "data.d2 AS data_d2 FROM data) AS anon_1 "
            "LEFT OUTER JOIN (SELECT data.id AS id FROM data) AS anon_2 "
            "ON anon_2.id = anon_1.data_id "
            "ORDER BY anon_1.data_id, anon_1.data_d1, anon_1.data_d2",
        )

        # tuple nesting still occurs
        eq_(
            joined.all(),
            [
                ((1, "d0d1", "d0d2"),),
                ((2, "d1d1", "d1d2"),),
                ((3, "d2d1", "d2d2"),),
                ((4, "d3d1", "d3d2"),),
                ((5, "d4d1", "d4d2"),),
                ((6, "d5d1", "d5d2"),),
                ((7, "d6d1", "d6d2"),),
                ((8, "d7d1", "d7d2"),),
                ((9, "d8d1", "d8d2"),),
                ((10, "d9d1", "d9d2"),),
            ],
        )

    def test_filter_by(self):
        Data = self.classes.Data

        b1 = Bundle("b1", Data.id, Data.d1, Data.d2)

        sess = Session()

        self.assert_compile(
            sess.query(b1).filter_by(d1="d1"),
            "SELECT data.id AS data_id, data.d1 AS data_d1, "
            "data.d2 AS data_d2 FROM data WHERE data.d1 = :d1_1",
        )

    def test_clause_expansion(self):
        Data = self.classes.Data

        b1 = Bundle("b1", Data.id, Data.d1, Data.d2)

        sess = Session()
        self.assert_compile(
            sess.query(Data).order_by(b1),
            "SELECT data.id AS data_id, data.d1 AS data_d1, "
            "data.d2 AS data_d2, data.d3 AS data_d3 FROM data "
            "ORDER BY data.id, data.d1, data.d2",
        )

        self.assert_compile(
            sess.query(func.row_number().over(order_by=b1)),
            "SELECT row_number() OVER (ORDER BY data.id, data.d1, data.d2) "
            "AS anon_1 FROM data",
        )