diff options
| author | Mike Bayer <mike_mp@zzzcomputing.com> | 2019-04-29 23:26:36 -0400 |
|---|---|---|
| committer | Mike Bayer <mike_mp@zzzcomputing.com> | 2019-05-18 17:46:10 -0400 |
| commit | f07e050c9ce4afdeb9c0c136dbcc547f7e5ac7b8 (patch) | |
| tree | 1b3cd7409ae2eddef635960126551d74f469acc1 /test | |
| parent | 614dfb5f5b5a2427d5d6ce0bc5f34bf0581bf698 (diff) | |
| download | sqlalchemy-f07e050c9ce4afdeb9c0c136dbcc547f7e5ac7b8.tar.gz | |
Implement new ClauseElement role and coercion system
A major refactoring of all the functions handle all detection of
Core argument types as well as perform coercions into a new class hierarchy
based on "roles", each of which identify a syntactical location within a
SQL statement. In contrast to the ClauseElement hierarchy that identifies
"what" each object is syntactically, the SQLRole hierarchy identifies
the "where does it go" of each object syntactically. From this we define
a consistent type checking and coercion system that establishes well
defined behviors.
This is a breakout of the patch that is reorganizing select()
constructs to no longer be in the FromClause hierarchy.
Also includes a rename of as_scalar() into scalar_subquery(); deprecates
automatic coercion to scalar_subquery().
Partially-fixes: #4617
Change-Id: I26f1e78898693c6b99ef7ea2f4e7dfd0e8e1a1bd
Diffstat (limited to 'test')
40 files changed, 689 insertions, 279 deletions
diff --git a/test/dialect/mssql/test_compiler.py b/test/dialect/mssql/test_compiler.py index 30a11d16b..498de763c 100644 --- a/test/dialect/mssql/test_compiler.py +++ b/test/dialect/mssql/test_compiler.py @@ -267,7 +267,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): stmt = table.update().values( val=select([other.c.newval]) .where(table.c.sym == other.c.sym) - .as_scalar() + .scalar_subquery() ) self.assert_compile( @@ -334,14 +334,14 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): t = table("sometable", column("somecolumn")) self.assert_compile( - t.select().where(t.c.somecolumn == t.select()), + t.select().where(t.c.somecolumn == t.select().scalar_subquery()), "SELECT sometable.somecolumn FROM " "sometable WHERE sometable.somecolumn = " "(SELECT sometable.somecolumn FROM " "sometable)", ) self.assert_compile( - t.select().where(t.c.somecolumn != t.select()), + t.select().where(t.c.somecolumn != t.select().scalar_subquery()), "SELECT sometable.somecolumn FROM " "sometable WHERE sometable.somecolumn != " "(SELECT sometable.somecolumn FROM " @@ -844,7 +844,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): t1 = table("t1", column("x", Integer), column("y", Integer)) t2 = table("t2", column("x", Integer), column("y", Integer)) - order_by = select([t2.c.y]).where(t1.c.x == t2.c.x).as_scalar() + order_by = select([t2.c.y]).where(t1.c.x == t2.c.x).scalar_subquery() s = ( select([t1]) .where(t1.c.x == 5) @@ -1135,7 +1135,7 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): idx = Index("test_idx_data_1", tbl.c.data, mssql_where=tbl.c.data > 1) self.assert_compile( schema.CreateIndex(idx), - "CREATE INDEX test_idx_data_1 ON test (data) WHERE data > 1" + "CREATE INDEX test_idx_data_1 ON test (data) WHERE data > 1", ) def test_index_ordering(self): diff --git a/test/dialect/mssql/test_query.py b/test/dialect/mssql/test_query.py index bf836fc14..4ecf0634c 100644 --- a/test/dialect/mssql/test_query.py +++ b/test/dialect/mssql/test_query.py @@ -130,7 +130,7 @@ class LegacySchemaAliasingTest(fixtures.TestBase, AssertsCompiledSQL): def test_column_subquery_to_alias(self): a1 = self.t2.alias("a1") - s = select([self.t2, select([a1.c.a]).as_scalar()]) + s = select([self.t2, select([a1.c.a]).scalar_subquery()]) self._assert_sql( s, "SELECT t2_1.a, t2_1.b, t2_1.c, " diff --git a/test/dialect/mysql/test_query.py b/test/dialect/mysql/test_query.py index e492f1e17..39485ae10 100644 --- a/test/dialect/mysql/test_query.py +++ b/test/dialect/mysql/test_query.py @@ -247,7 +247,7 @@ class AnyAllTest(fixtures.TablesTest): def test_any_w_comparator(self): stuff = self.tables.stuff stmt = select([stuff.c.id]).where( - stuff.c.value > any_(select([stuff.c.value])) + stuff.c.value > any_(select([stuff.c.value]).scalar_subquery()) ) eq_(testing.db.execute(stmt).fetchall(), [(2,), (3,), (4,), (5,)]) @@ -255,13 +255,13 @@ class AnyAllTest(fixtures.TablesTest): def test_all_w_comparator(self): stuff = self.tables.stuff stmt = select([stuff.c.id]).where( - stuff.c.value >= all_(select([stuff.c.value])) + stuff.c.value >= all_(select([stuff.c.value]).scalar_subquery()) ) eq_(testing.db.execute(stmt).fetchall(), [(5,)]) def test_any_literal(self): stuff = self.tables.stuff - stmt = select([4 == any_(select([stuff.c.value]))]) + stmt = select([4 == any_(select([stuff.c.value]).scalar_subquery())]) is_(testing.db.execute(stmt).scalar(), True) diff --git a/test/ext/declarative/test_basic.py b/test/ext/declarative/test_basic.py index 3fe2f1bfe..b6c911813 100644 --- a/test/ext/declarative/test_basic.py +++ b/test/ext/declarative/test_basic.py @@ -705,7 +705,7 @@ class DeclarativeTest(DeclarativeTestBase): rel = relationship("User", primaryjoin="User.id==Bar.__table__.id") assert_raises_message( - exc.InvalidRequestError, + AttributeError, "does not have a mapped column named " "'__table__'", configure_mappers, ) @@ -1469,7 +1469,7 @@ class DeclarativeTest(DeclarativeTestBase): User.address_count = sa.orm.column_property( sa.select([sa.func.count(Address.id)]) .where(Address.user_id == User.id) - .as_scalar() + .scalar_subquery() ) Base.metadata.create_all() u1 = User( @@ -1514,9 +1514,9 @@ class DeclarativeTest(DeclarativeTestBase): # this doesn't really gain us anything. but if # one is used, lets have it function as expected... return sa.orm.column_property( - sa.select([sa.func.count(Address.id)]).where( - Address.user_id == cls.id - ) + sa.select([sa.func.count(Address.id)]) + .where(Address.user_id == cls.id) + .scalar_subquery() ) Base.metadata.create_all() @@ -1616,7 +1616,7 @@ class DeclarativeTest(DeclarativeTestBase): adr_count = sa.orm.column_property( sa.select( [sa.func.count(Address.id)], Address.user_id == id - ).as_scalar() + ).scalar_subquery() ) addresses = relationship(Address) @@ -1920,7 +1920,7 @@ class DeclarativeTest(DeclarativeTestBase): User.address_count = sa.orm.column_property( sa.select([sa.func.count(Address.id)]) .where(Address.user_id == User.id) - .as_scalar() + .scalar_subquery() ) Base.metadata.create_all() u1 = User( diff --git a/test/ext/declarative/test_mixin.py b/test/ext/declarative/test_mixin.py index ef9bbd354..df7dea77c 100644 --- a/test/ext/declarative/test_mixin.py +++ b/test/ext/declarative/test_mixin.py @@ -1851,7 +1851,7 @@ class DeclaredAttrTest(DeclarativeTestBase, testing.AssertsCompiledSQL): return column_property( select([func.count(Address.id)]) .where(Address.user_id == cls.id) - .as_scalar() + .scalar_subquery() ) class Address(Base): diff --git a/test/ext/test_baked.py b/test/ext/test_baked.py index 55cd9376b..00c6a78b4 100644 --- a/test/ext/test_baked.py +++ b/test/ext/test_baked.py @@ -765,9 +765,11 @@ class ResultTest(BakedTest): ) main_bq = self.bakery( - lambda s: s.query(Address.id, sub_bq.to_query(s).as_scalar()) + lambda s: s.query(Address.id, sub_bq.to_query(s).scalar_subquery()) + ) + main_bq += lambda q: q.filter( + sub_bq.to_query(q).scalar_subquery() == "ed" ) - main_bq += lambda q: q.filter(sub_bq.to_query(q).as_scalar() == "ed") main_bq += lambda q: q.order_by(Address.id) sess = Session() diff --git a/test/orm/inheritance/test_assorted_poly.py b/test/orm/inheritance/test_assorted_poly.py index 525824669..07ffd9385 100644 --- a/test/orm/inheritance/test_assorted_poly.py +++ b/test/orm/inheritance/test_assorted_poly.py @@ -2184,6 +2184,7 @@ class CorrelateExceptWPolyAdaptTest( select([func.count(Superclass.id)]) .where(Superclass.common_id == id) .correlate_except(Superclass) + .scalar_subquery() ) if not use_correlate_except: @@ -2191,6 +2192,7 @@ class CorrelateExceptWPolyAdaptTest( select([func.count(Superclass.id)]) .where(Superclass.common_id == Common.id) .correlate(Common) + .scalar_subquery() ) return Common, Superclass diff --git a/test/orm/inheritance/test_basic.py b/test/orm/inheritance/test_basic.py index 59ecc7c98..472dafcc4 100644 --- a/test/orm/inheritance/test_basic.py +++ b/test/orm/inheritance/test_basic.py @@ -344,8 +344,8 @@ class PolymorphicOnNotLocalTest(fixtures.MappedTest): assert_raises_message( sa_exc.ArgumentError, - "Only direct column-mapped property or " - "SQL expression can be passed for polymorphic_on", + r"Column expression or string key expected for argument " + r"'polymorphic_on'; got .*function", go, ) diff --git a/test/orm/inheritance/test_polymorphic_rel.py b/test/orm/inheritance/test_polymorphic_rel.py index c16573b23..bbf0d472a 100644 --- a/test/orm/inheritance/test_polymorphic_rel.py +++ b/test/orm/inheritance/test_polymorphic_rel.py @@ -1295,7 +1295,7 @@ class _PolymorphicTestBase(object): subq = ( sess.query(engineers.c.person_id) .filter(Engineer.primary_language == "java") - .statement.as_scalar() + .statement.scalar_subquery() ) eq_(sess.query(Person).filter(Person.person_id.in_(subq)).one(), e1) @@ -1634,15 +1634,16 @@ class _PolymorphicTestBase(object): # this for a long time did not work with PolymorphicAliased and # PolymorphicUnions, which was due to the no_replacement_traverse - # annotation added to query.statement which then went into as_scalar(). - # this is removed as of :ticket:`4304` so now works. + # annotation added to query.statement which then went into + # scalar_subquery(). this is removed as of :ticket:`4304` so now + # works. eq_( sess.query(Person.name) .filter( sess.query(Company.name) .filter(Company.company_id == Person.company_id) .correlate(Person) - .as_scalar() + .scalar_subquery() == "Elbonia, Inc." ) .all(), @@ -1660,7 +1661,7 @@ class _PolymorphicTestBase(object): sess.query(Company.name) .filter(Company.company_id == paliased.company_id) .correlate(paliased) - .as_scalar() + .scalar_subquery() == "Elbonia, Inc." ) .all(), @@ -1678,7 +1679,7 @@ class _PolymorphicTestBase(object): sess.query(Company.name) .filter(Company.company_id == paliased.company_id) .correlate(paliased) - .as_scalar() + .scalar_subquery() == "Elbonia, Inc." ) .all(), @@ -1720,7 +1721,7 @@ class PolymorphicTest(_PolymorphicTestBase, _Polymorphic): sess.query(Company.name) .filter(Company.company_id == p_poly.company_id) .correlate(p_poly) - .as_scalar() + .scalar_subquery() == "Elbonia, Inc." ) .all(), @@ -1739,7 +1740,7 @@ class PolymorphicTest(_PolymorphicTestBase, _Polymorphic): sess.query(Company.name) .filter(Company.company_id == p_poly.company_id) .correlate(p_poly) - .as_scalar() + .scalar_subquery() == "Elbonia, Inc." ) .all(), diff --git a/test/orm/inheritance/test_single.py b/test/orm/inheritance/test_single.py index 1b28974b7..a54cfbe93 100644 --- a/test/orm/inheritance/test_single.py +++ b/test/orm/inheritance/test_single.py @@ -962,7 +962,7 @@ class RelationshipToSingleTest( .select_from(Engineer) .filter(Engineer.company_id == Company.company_id) .correlate(Company) - .as_scalar() + .scalar_subquery() ) self.assert_compile( diff --git a/test/orm/test_collection.py b/test/orm/test_collection.py index 83f4f4451..eb0df3107 100644 --- a/test/orm/test_collection.py +++ b/test/orm/test_collection.py @@ -1827,15 +1827,15 @@ class DictHelpersTest(fixtures.MappedTest): def test_column_mapped_assertions(self): assert_raises_message( sa_exc.ArgumentError, - "Column-based expression object expected " - "for argument 'mapping_spec'; got: 'a'", + "Column expression expected " + "for argument 'mapping_spec'; got 'a'.", collections.column_mapped_collection, "a", ) assert_raises_message( sa_exc.ArgumentError, - "Column-based expression object expected " - "for argument 'mapping_spec'; got: 'a'", + "Column expression expected " + "for argument 'mapping_spec'; got .*TextClause.", collections.column_mapped_collection, text("a"), ) diff --git a/test/orm/test_deprecations.py b/test/orm/test_deprecations.py index 679b3ec5b..c5938416c 100644 --- a/test/orm/test_deprecations.py +++ b/test/orm/test_deprecations.py @@ -550,6 +550,17 @@ class StrongIdentityMapTest(_fixtures.FixtureTest): class DeprecatedMapperTest(_fixtures.FixtureTest, AssertsCompiledSQL): __dialect__ = "default" + def test_query_as_scalar(self): + users, User = self.tables.users, self.classes.User + + mapper(User, users) + s = Session() + with assertions.expect_deprecated( + r"The Query.as_scalar\(\) method is deprecated and will " + "be removed in a future release." + ): + s.query(User).as_scalar() + def test_cancel_order_by(self): users, User = self.tables.users, self.classes.User diff --git a/test/orm/test_eager_relations.py b/test/orm/test_eager_relations.py index 4adf9a72f..499803543 100644 --- a/test/orm/test_eager_relations.py +++ b/test/orm/test_eager_relations.py @@ -3258,7 +3258,7 @@ class SubqueryAliasingTest(fixtures.MappedTest, testing.AssertsCompiledSQL): b_table.c.a_id == a_table.c.id ) - self._fixture({"summation": column_property(cp)}) + self._fixture({"summation": column_property(cp.scalar_subquery())}) self.assert_compile( create_session() .query(A) @@ -3281,7 +3281,7 @@ class SubqueryAliasingTest(fixtures.MappedTest, testing.AssertsCompiledSQL): b_table.c.a_id == a_table.c.id ) - self._fixture({"summation": column_property(cp)}) + self._fixture({"summation": column_property(cp.scalar_subquery())}) self.assert_compile( create_session() .query(A) @@ -3306,7 +3306,7 @@ class SubqueryAliasingTest(fixtures.MappedTest, testing.AssertsCompiledSQL): .correlate(a_table) ) - self._fixture({"summation": column_property(cp)}) + self._fixture({"summation": column_property(cp.scalar_subquery())}) self.assert_compile( create_session() .query(A) @@ -3330,7 +3330,7 @@ class SubqueryAliasingTest(fixtures.MappedTest, testing.AssertsCompiledSQL): select([func.sum(b_table.c.value)]) .where(b_table.c.a_id == a_table.c.id) .correlate(a_table) - .as_scalar() + .scalar_subquery() ) # up until 0.8, this was ordering by a new subquery. @@ -3360,7 +3360,7 @@ class SubqueryAliasingTest(fixtures.MappedTest, testing.AssertsCompiledSQL): select([func.sum(b_table.c.value)]) .where(b_table.c.a_id == a_table.c.id) .correlate(a_table) - .as_scalar() + .scalar_subquery() .label("foo") ) self.assert_compile( @@ -3387,7 +3387,7 @@ class SubqueryAliasingTest(fixtures.MappedTest, testing.AssertsCompiledSQL): select([func.sum(b_table.c.value)]) .where(b_table.c.a_id == a_table.c.id) .correlate(a_table) - .as_scalar() + .scalar_subquery() ) # test a different unary operator self.assert_compile( @@ -4689,7 +4689,7 @@ class SubqueryTest(fixtures.MappedTest): tag_score = tag_score.label(labelname) user_score = user_score.label(labelname) else: - user_score = user_score.as_scalar() + user_score = user_score.scalar_subquery() mapper( Tag, @@ -4798,40 +4798,28 @@ class CorrelatedSubqueryTest(fixtures.MappedTest): ) def test_labeled_on_date_noalias(self): - self._do_test("label", True, False) + self._do_test(True, True, False) def test_scalar_on_date_noalias(self): - self._do_test("scalar", True, False) - - def test_plain_on_date_noalias(self): - self._do_test("none", True, False) + self._do_test(False, True, False) def test_labeled_on_limitid_noalias(self): - self._do_test("label", False, False) + self._do_test(True, False, False) def test_scalar_on_limitid_noalias(self): - self._do_test("scalar", False, False) - - def test_plain_on_limitid_noalias(self): - self._do_test("none", False, False) + self._do_test(False, False, False) def test_labeled_on_date_alias(self): - self._do_test("label", True, True) + self._do_test(True, True, True) def test_scalar_on_date_alias(self): - self._do_test("scalar", True, True) - - def test_plain_on_date_alias(self): - self._do_test("none", True, True) + self._do_test(False, True, True) def test_labeled_on_limitid_alias(self): - self._do_test("label", False, True) + self._do_test(True, False, True) def test_scalar_on_limitid_alias(self): - self._do_test("scalar", False, True) - - def test_plain_on_limitid_alias(self): - self._do_test("none", False, True) + self._do_test(False, False, True) def _do_test(self, labeled, ondate, aliasstuff): stuff, users = self.tables.stuff, self.tables.users @@ -4843,7 +4831,6 @@ class CorrelatedSubqueryTest(fixtures.MappedTest): pass mapper(Stuff, stuff) - if aliasstuff: salias = stuff.alias() else: @@ -4879,11 +4866,11 @@ class CorrelatedSubqueryTest(fixtures.MappedTest): else: operator = operators.eq - if labeled == "label": + if labeled: stuff_view = stuff_view.label("foo") operator = operators.eq - elif labeled == "scalar": - stuff_view = stuff_view.as_scalar() + else: + stuff_view = stuff_view.scalar_subquery() if ondate: mapper( diff --git a/test/orm/test_froms.py b/test/orm/test_froms.py index 1cccfff26..3cec10e68 100644 --- a/test/orm/test_froms.py +++ b/test/orm/test_froms.py @@ -161,79 +161,79 @@ class QueryCorrelatesLikeSelect(QueryTest, AssertsCompiledSQL): "WHERE addresses.user_id = users.id) AS anon_1 FROM users" ) - def test_as_scalar_select_auto_correlate(self): + def test_scalar_subquery_select_auto_correlate(self): addresses, users = self.tables.addresses, self.tables.users query = select( [func.count(addresses.c.id)], addresses.c.user_id == users.c.id - ).as_scalar() + ).scalar_subquery() query = select([users.c.name.label("users_name"), query]) self.assert_compile( query, self.query_correlated, dialect=default.DefaultDialect() ) - def test_as_scalar_select_explicit_correlate(self): + def test_scalar_subquery_select_explicit_correlate(self): addresses, users = self.tables.addresses, self.tables.users query = ( select( [func.count(addresses.c.id)], addresses.c.user_id == users.c.id ) .correlate(users) - .as_scalar() + .scalar_subquery() ) query = select([users.c.name.label("users_name"), query]) self.assert_compile( query, self.query_correlated, dialect=default.DefaultDialect() ) - def test_as_scalar_select_correlate_off(self): + def test_scalar_subquery_select_correlate_off(self): addresses, users = self.tables.addresses, self.tables.users query = ( select( [func.count(addresses.c.id)], addresses.c.user_id == users.c.id ) .correlate(None) - .as_scalar() + .scalar_subquery() ) query = select([users.c.name.label("users_name"), query]) self.assert_compile( query, self.query_not_correlated, dialect=default.DefaultDialect() ) - def test_as_scalar_query_auto_correlate(self): + def test_scalar_subquery_query_auto_correlate(self): sess = create_session() Address, User = self.classes.Address, self.classes.User query = ( sess.query(func.count(Address.id)) .filter(Address.user_id == User.id) - .as_scalar() + .scalar_subquery() ) query = sess.query(User.name, query) self.assert_compile( query, self.query_correlated, dialect=default.DefaultDialect() ) - def test_as_scalar_query_explicit_correlate(self): + def test_scalar_subquery_query_explicit_correlate(self): sess = create_session() Address, User = self.classes.Address, self.classes.User query = ( sess.query(func.count(Address.id)) .filter(Address.user_id == User.id) .correlate(self.tables.users) - .as_scalar() + .scalar_subquery() ) query = sess.query(User.name, query) self.assert_compile( query, self.query_correlated, dialect=default.DefaultDialect() ) - def test_as_scalar_query_correlate_off(self): + def test_scalar_subquery_query_correlate_off(self): sess = create_session() Address, User = self.classes.Address, self.classes.User query = ( sess.query(func.count(Address.id)) .filter(Address.user_id == User.id) .correlate(None) - .as_scalar() + .scalar_subquery() ) query = sess.query(User.name, query) self.assert_compile( @@ -3243,7 +3243,7 @@ class ExternalColumnsTest(QueryTest): users.c.id == addresses.c.user_id, ) .correlate(users) - .as_scalar() + .scalar_subquery() ), }, ) @@ -3398,7 +3398,9 @@ class ExternalColumnsTest(QueryTest): select( [func.count(addresses.c.id)], users.c.id == addresses.c.user_id, - ).correlate(users) + ) + .correlate(users) + .scalar_subquery() ), }, ) diff --git a/test/orm/test_lazy_relations.py b/test/orm/test_lazy_relations.py index 78680701e..8d1e82fb5 100644 --- a/test/orm/test_lazy_relations.py +++ b/test/orm/test_lazy_relations.py @@ -1290,7 +1290,7 @@ class CorrelatedTest(fixtures.MappedTest): Stuff, primaryjoin=sa.and_( user_t.c.id == stuff.c.user_id, - stuff.c.id == (stuff_view.as_scalar()), + stuff.c.id == (stuff_view.scalar_subquery()), ), ) }, diff --git a/test/orm/test_mapper.py b/test/orm/test_mapper.py index 93cf19fae..fa1f1fdf8 100644 --- a/test/orm/test_mapper.py +++ b/test/orm/test_mapper.py @@ -764,7 +764,7 @@ class MapperTest(_fixtures.FixtureTest, AssertsCompiledSQL): expr = User.name + "name" expr2 = sa.select([User.name, users.c.id]) m.add_property("x", column_property(expr)) - m.add_property("y", column_property(expr2)) + m.add_property("y", column_property(expr2.scalar_subquery())) assert User.x.property.columns[0] is not expr assert User.x.property.columns[0].element.left is users.c.name diff --git a/test/orm/test_query.py b/test/orm/test_query.py index 86b304be9..27176d6fb 100644 --- a/test/orm/test_query.py +++ b/test/orm/test_query.py @@ -467,7 +467,7 @@ class RawSelectTest(QueryTest, AssertsCompiledSQL): select([func.count(Address.id)]) .where(User.id == Address.user_id) .correlate(User) - .as_scalar(), + .scalar_subquery(), ] ), "SELECT users.name, addresses.id, " @@ -489,7 +489,7 @@ class RawSelectTest(QueryTest, AssertsCompiledSQL): select([func.count(Address.id)]) .where(uu.id == Address.user_id) .correlate(uu) - .as_scalar(), + .scalar_subquery(), ] ), # for a long time, "uu.id = address.user_id" was reversed; @@ -1072,7 +1072,7 @@ class InvalidGenerationsTest(QueryTest, AssertsCompiledSQL): assert_raises_message( sa_exc.ArgumentError, - "Object .*User.* is not legal as a SQL literal value", + "SQL expression element expected, got .*User", distinct, User, ) @@ -1080,7 +1080,7 @@ class InvalidGenerationsTest(QueryTest, AssertsCompiledSQL): ua = aliased(User) assert_raises_message( sa_exc.ArgumentError, - "Object .*User.* is not legal as a SQL literal value", + "SQL expression element expected, got .*User", distinct, ua, ) @@ -1088,21 +1088,21 @@ class InvalidGenerationsTest(QueryTest, AssertsCompiledSQL): s = Session() assert_raises_message( sa_exc.ArgumentError, - "Object .*User.* is not legal as a SQL literal value", + "SQL expression element or literal value expected, got .*User", lambda: s.query(User).filter(User.name == User), ) u1 = User() assert_raises_message( sa_exc.ArgumentError, - "Object .*User.* is not legal as a SQL literal value", + "SQL expression element expected, got .*User", distinct, u1, ) assert_raises_message( sa_exc.ArgumentError, - "Object .*User.* is not legal as a SQL literal value", + "SQL expression element or literal value expected, got .*User", lambda: s.query(User).filter(User.name == u1), ) @@ -1757,16 +1757,17 @@ class ExpressionTest(QueryTest, AssertsCompiledSQL): session = create_session() - q = session.query(User.id).filter(User.id == 7) + q = session.query(User.id).filter(User.id == 7).scalar_subquery() q = session.query(Address).filter(Address.user_id == q) + assert isinstance(q._criterion.right, expression.ColumnElement) self.assert_compile( q, "SELECT addresses.id AS addresses_id, addresses.user_id " "AS addresses_user_id, addresses.email_address AS " "addresses_email_address FROM addresses WHERE " - "addresses.user_id = (SELECT users.id AS users_id " + "addresses.user_id = (SELECT users.id " "FROM users WHERE users.id = :id_1)", ) @@ -1842,12 +1843,12 @@ class ExpressionTest(QueryTest, AssertsCompiledSQL): "WHERE users.id = :id_1) AS foo", ) - def test_as_scalar(self): + def test_scalar_subquery(self): User = self.classes.User session = create_session() - q = session.query(User.id).filter(User.id == 7).as_scalar() + q = session.query(User.id).filter(User.id == 7).scalar_subquery() self.assert_compile( session.query(User).filter(User.id.in_(q)), @@ -1866,7 +1867,7 @@ class ExpressionTest(QueryTest, AssertsCompiledSQL): session.query(User.id) .filter(User.id == bindparam("foo")) .params(foo=7) - .subquery() + .scalar_subquery() ) q = session.query(User).filter(User.id.in_(q)) @@ -2081,6 +2082,8 @@ class ColumnPropertyTest(_fixtures.FixtureTest, AssertsCompiledSQL): ) if label: stmt = stmt.label("email_ad") + else: + stmt = stmt.scalar_subquery() mapper( User, @@ -5491,7 +5494,7 @@ class SessionBindTest(QueryTest): column_property( select([func.sum(Address.id)]) .where(Address.user_id == User.id) - .as_scalar() + .scalar_subquery() ), ) session = Session() diff --git a/test/orm/test_rel_fn.py b/test/orm/test_rel_fn.py index 5e6ac53fe..259d5ea9c 100644 --- a/test/orm/test_rel_fn.py +++ b/test/orm/test_rel_fn.py @@ -470,7 +470,7 @@ class _JoinFixtures(object): self.left, self.right, primaryjoin=self.left.c.id == func.foo(self.right.c.lid), - consider_as_foreign_keys=[self.right.c.lid], + consider_as_foreign_keys={self.right.c.lid}, **kw ) @@ -480,10 +480,10 @@ class _JoinFixtures(object): self.composite_multi_ref, self.composite_target, self.composite_multi_ref, - consider_as_foreign_keys=[ + consider_as_foreign_keys={ self.composite_multi_ref.c.uid2, self.composite_multi_ref.c.oid, - ], + }, **kw ) @@ -1099,10 +1099,10 @@ class DetermineJoinTest(_JoinFixtures, fixtures.TestBase, AssertsCompiledSQL): self.m2mleft, self.m2mright, secondary=self.m2msecondary_ambig_fks, - consider_as_foreign_keys=[ + consider_as_foreign_keys={ self.m2msecondary_ambig_fks.c.lid1, self.m2msecondary_ambig_fks.c.rid1, - ], + }, ) def test_determine_join_w_fks_ambig_m2m(self): diff --git a/test/orm/test_relationships.py b/test/orm/test_relationships.py index 652b8bacd..494ec2b0d 100644 --- a/test/orm/test_relationships.py +++ b/test/orm/test_relationships.py @@ -2288,9 +2288,8 @@ class JoinConditionErrorTest(fixtures.TestBase): assert_raises_message( sa.exc.ArgumentError, - "Column-based expression object expected " - "for argument '%s'; got: '%s', type %r" - % (argname, arg[0], type(arg[0])), + "Column expression expected " + "for argument '%s'; got '%s'" % (argname, arg[0]), configure_mappers, ) diff --git a/test/orm/test_update_delete.py b/test/orm/test_update_delete.py index 9e3f8074f..217a4f77a 100644 --- a/test/orm/test_update_delete.py +++ b/test/orm/test_update_delete.py @@ -326,13 +326,15 @@ class UpdateDeleteTest(fixtures.MappedTest): assert_raises( exc.InvalidRequestError, sess.query(User) - .filter(User.name == select([func.max(User.name)])) + .filter( + User.name == select([func.max(User.name)]).scalar_subquery() + ) .delete, synchronize_session="evaluate", ) sess.query(User).filter( - User.name == select([func.max(User.name)]) + User.name == select([func.max(User.name)]).scalar_subquery() ).delete(synchronize_session="fetch") assert john not in sess @@ -969,7 +971,7 @@ class UpdateDeleteFromTest(fixtures.MappedTest): subq = ( s.query(func.max(Document.title).label("title")) .group_by(Document.user_id) - .subquery() + .scalar_subquery() ) s.query(Document).filter(Document.title.in_(subq)).update( @@ -999,7 +1001,7 @@ class UpdateDeleteFromTest(fixtures.MappedTest): subq = ( s.query(func.max(Document.title).label("title")) .group_by(Document.user_id) - .subquery() + .scalar_subquery() ) # this would work with Firebird if you do literal_column('1') diff --git a/test/profiles.txt b/test/profiles.txt index 5c51d623f..d12b3c4ae 100644 --- a/test/profiles.txt +++ b/test/profiles.txt @@ -1,15 +1,15 @@ # /home/classic/dev/sqlalchemy/test/profiles.txt # This file is written out on a per-environment basis. -# For each test in aaa_profiling, the corresponding function and +# For each test in aaa_profiling, the corresponding function and # environment is located within this file. If it doesn't exist, # the test is skipped. -# If a callcount does exist, it is compared to what we received. +# If a callcount does exist, it is compared to what we received. # assertions are raised if the counts do not match. -# -# To add a new callcount test, apply the function_call_count -# decorator and re-run the tests using the --write-profiles +# +# To add a new callcount test, apply the function_call_count +# decorator and re-run the tests using the --write-profiles # option - this file will be rewritten including the new count. -# +# # TEST: test.aaa_profiling.test_compiler.CompileTest.test_insert @@ -803,14 +803,14 @@ test.aaa_profiling.test_resultset.ResultSetTest.test_unicode 3.7_sqlite_pysqlite # TEST: test.aaa_profiling.test_zoomark.ZooMarkTest.test_invocation -test.aaa_profiling.test_zoomark.ZooMarkTest.test_invocation 2.7_postgresql_psycopg2_dbapiunicode_cextensions 6010,303,3905,12590,1177,2109,2565 -test.aaa_profiling.test_zoomark.ZooMarkTest.test_invocation 2.7_postgresql_psycopg2_dbapiunicode_nocextensions 6054,303,4025,13894,1292,2122,2796 -test.aaa_profiling.test_zoomark.ZooMarkTest.test_invocation 3.7_postgresql_psycopg2_dbapiunicode_cextensions 5742,282,3865,12494,1163,2042,2604 -test.aaa_profiling.test_zoomark.ZooMarkTest.test_invocation 3.7_postgresql_psycopg2_dbapiunicode_nocextensions 5808,282,3993,13758,1273,2061,2816 +test.aaa_profiling.test_zoomark.ZooMarkTest.test_invocation 2.7_postgresql_psycopg2_dbapiunicode_cextensions 6401,324,3953,12769,1200,2189,2624 +test.aaa_profiling.test_zoomark.ZooMarkTest.test_invocation 2.7_postgresql_psycopg2_dbapiunicode_nocextensions 6445,324,4073,14073,1315,2202,2855 +test.aaa_profiling.test_zoomark.ZooMarkTest.test_invocation 3.7_postgresql_psycopg2_dbapiunicode_cextensions 6116,303,3913,12679,1186,2122,2667 +test.aaa_profiling.test_zoomark.ZooMarkTest.test_invocation 3.7_postgresql_psycopg2_dbapiunicode_nocextensions 6165,303,4041,13945,1295,2141,2883 # TEST: test.aaa_profiling.test_zoomark_orm.ZooMarkTest.test_invocation -test.aaa_profiling.test_zoomark_orm.ZooMarkTest.test_invocation 2.7_postgresql_psycopg2_dbapiunicode_cextensions 6627,413,6961,18335,1191,2723 -test.aaa_profiling.test_zoomark_orm.ZooMarkTest.test_invocation 2.7_postgresql_psycopg2_dbapiunicode_nocextensions 6719,418,7081,19404,1297,2758 -test.aaa_profiling.test_zoomark_orm.ZooMarkTest.test_invocation 3.7_postgresql_psycopg2_dbapiunicode_cextensions 6601,403,7149,18918,1178,2790 -test.aaa_profiling.test_zoomark_orm.ZooMarkTest.test_invocation 3.7_postgresql_psycopg2_dbapiunicode_nocextensions 6696,408,7285,20029,1280,2831 +test.aaa_profiling.test_zoomark_orm.ZooMarkTest.test_invocation 2.7_postgresql_psycopg2_dbapiunicode_cextensions 6957,409,7143,18720,1214,2829 +test.aaa_profiling.test_zoomark_orm.ZooMarkTest.test_invocation 2.7_postgresql_psycopg2_dbapiunicode_nocextensions 7053,414,7263,19789,1320,2864 +test.aaa_profiling.test_zoomark_orm.ZooMarkTest.test_invocation 3.7_postgresql_psycopg2_dbapiunicode_cextensions 6946,400,7331,19307,1201,2895 +test.aaa_profiling.test_zoomark_orm.ZooMarkTest.test_invocation 3.7_postgresql_psycopg2_dbapiunicode_nocextensions 7046,405,7467,20418,1302,2935 diff --git a/test/sql/test_case_statement.py b/test/sql/test_case_statement.py index 7bb612cfa..491ff42bc 100644 --- a/test/sql/test_case_statement.py +++ b/test/sql/test_case_statement.py @@ -131,7 +131,7 @@ class CaseTest(fixtures.TestBase, AssertsCompiledSQL): def test_literal_interpretation_ambiguous(self): assert_raises_message( exc.ArgumentError, - r"Ambiguous literal: 'x'. Use the 'text\(\)' function", + r"Column expression expected, got 'x'", case, [("x", "y")], ) @@ -139,7 +139,7 @@ class CaseTest(fixtures.TestBase, AssertsCompiledSQL): def test_literal_interpretation_ambiguous_tuple(self): assert_raises_message( exc.ArgumentError, - r"Ambiguous literal: \('x', 'y'\). Use the 'text\(\)' function", + r"Column expression expected, got \('x', 'y'\)", case, [(("x", "y"), "z")], ) diff --git a/test/sql/test_compare.py b/test/sql/test_compare.py index 3608851ed..f2feea757 100644 --- a/test/sql/test_compare.py +++ b/test/sql/test_compare.py @@ -243,8 +243,8 @@ class CompareAndCopyTest(fixtures.TestBase): FromGrouping(table_a.alias("b")), ), lambda: ( - select([table_a.c.a]).as_scalar(), - select([table_a.c.a]).where(table_a.c.b == 5).as_scalar(), + select([table_a.c.a]).scalar_subquery(), + select([table_a.c.a]).where(table_a.c.b == 5).scalar_subquery(), ), lambda: ( exists().where(table_a.c.a == 5), @@ -291,6 +291,7 @@ class CompareAndCopyTest(fixtures.TestBase): and "__init__" in cls.__dict__ and not issubclass(cls, (Annotated)) and "orm" not in cls.__module__ + and "compiler" not in cls.__module__ and "crud" not in cls.__module__ and "dialects" not in cls.__module__ # TODO: dialects? ).difference({ColumnElement, UnaryExpression}) diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index 9d6e17a1d..e012c2713 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -71,7 +71,6 @@ from sqlalchemy.sql import column from sqlalchemy.sql import compiler from sqlalchemy.sql import label from sqlalchemy.sql import table -from sqlalchemy.sql.expression import _literal_as_text from sqlalchemy.sql.expression import ClauseList from sqlalchemy.sql.expression import HasPrefixes from sqlalchemy.testing import assert_raises @@ -165,7 +164,8 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): "columns; use this object directly within a " "column-level expression.", lambda: hasattr( - select([table1.c.myid]).as_scalar().self_group(), "columns" + select([table1.c.myid]).scalar_subquery().self_group(), + "columns", ), ) assert_raises_message( @@ -174,14 +174,17 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): "columns; use this object directly within a " "column-level expression.", lambda: hasattr( - select([table1.c.myid]).as_scalar(), "columns" + select([table1.c.myid]).scalar_subquery(), "columns" ), ) else: assert not hasattr( - select([table1.c.myid]).as_scalar().self_group(), "columns" + select([table1.c.myid]).scalar_subquery().self_group(), + "columns", + ) + assert not hasattr( + select([table1.c.myid]).scalar_subquery(), "columns" ) - assert not hasattr(select([table1.c.myid]).as_scalar(), "columns") def test_prefix_constructor(self): class Pref(HasPrefixes): @@ -327,7 +330,12 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): ) def test_select_precol_compile_ordering(self): - s1 = select([column("x")]).select_from(text("a")).limit(5).as_scalar() + s1 = ( + select([column("x")]) + .select_from(text("a")) + .limit(5) + .scalar_subquery() + ) s2 = select([s1]).limit(10) class MyCompiler(compiler.SQLCompiler): @@ -631,7 +639,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): ) self.assert_compile( - exists(s.as_scalar()), + exists(s.scalar_subquery()), "EXISTS (SELECT mytable.myid FROM mytable " "WHERE mytable.myid = :myid_1)", ) @@ -757,19 +765,9 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( table1.select( table1.c.myid - == select([table1.c.myid], table1.c.name == "jack") - ), - "SELECT mytable.myid, mytable.name, " - "mytable.description FROM mytable WHERE " - "mytable.myid = (SELECT mytable.myid FROM " - "mytable WHERE mytable.name = :name_1)", - ) - self.assert_compile( - table1.select( - table1.c.myid == select( [table2.c.otherid], table1.c.name == table2.c.othername - ) + ).scalar_subquery() ), "SELECT mytable.myid, mytable.name, " "mytable.description FROM mytable WHERE " @@ -822,7 +820,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): order_by=[ select( [table2.c.otherid], table1.c.myid == table2.c.otherid - ) + ).scalar_subquery() ] ), "SELECT mytable.myid, mytable.name, " @@ -831,42 +829,22 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): "myothertable WHERE mytable.myid = " "myothertable.otherid)", ) - self.assert_compile( - table1.select( - order_by=[ - desc( - select( - [table2.c.otherid], - table1.c.myid == table2.c.otherid, - ) - ) - ] - ), - "SELECT mytable.myid, mytable.name, " - "mytable.description FROM mytable ORDER BY " - "(SELECT myothertable.otherid FROM " - "myothertable WHERE mytable.myid = " - "myothertable.otherid) DESC", - ) def test_scalar_select(self): - assert_raises_message( - exc.InvalidRequestError, - r"Select objects don't have a type\. Call as_scalar\(\) " - r"on this Select object to return a 'scalar' " - r"version of this Select\.", - func.coalesce, - select([table1.c.myid]), + + self.assert_compile( + func.coalesce(select([table1.c.myid]).scalar_subquery()), + "coalesce((SELECT mytable.myid FROM mytable))", ) - s = select([table1.c.myid], correlate=False).as_scalar() + s = select([table1.c.myid], correlate=False).scalar_subquery() self.assert_compile( select([table1, s]), "SELECT mytable.myid, mytable.name, " "mytable.description, (SELECT mytable.myid " "FROM mytable) AS anon_1 FROM mytable", ) - s = select([table1.c.myid]).as_scalar() + s = select([table1.c.myid]).scalar_subquery() self.assert_compile( select([table2, s]), "SELECT myothertable.otherid, " @@ -874,7 +852,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): "mytable.myid FROM mytable) AS anon_1 FROM " "myothertable", ) - s = select([table1.c.myid]).correlate(None).as_scalar() + s = select([table1.c.myid]).correlate(None).scalar_subquery() self.assert_compile( select([table1, s]), "SELECT mytable.myid, mytable.name, " @@ -882,17 +860,17 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): "FROM mytable) AS anon_1 FROM mytable", ) - s = select([table1.c.myid]).as_scalar() + s = select([table1.c.myid]).scalar_subquery() s2 = s.where(table1.c.myid == 5) self.assert_compile( s2, "(SELECT mytable.myid FROM mytable WHERE mytable.myid = :myid_1)", ) self.assert_compile(s, "(SELECT mytable.myid FROM mytable)") - # test that aliases use as_scalar() when used in an explicitly + # test that aliases use scalar_subquery() when used in an explicitly # scalar context - s = select([table1.c.myid]).alias() + s = select([table1.c.myid]).scalar_subquery() self.assert_compile( select([table1.c.myid]).where(table1.c.myid == s), "SELECT mytable.myid FROM mytable WHERE " @@ -902,10 +880,9 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile( select([table1.c.myid]).where(s > table1.c.myid), "SELECT mytable.myid FROM mytable WHERE " - "mytable.myid < (SELECT mytable.myid FROM " - "mytable)", + "(SELECT mytable.myid FROM mytable) > mytable.myid", ) - s = select([table1.c.myid]).as_scalar() + s = select([table1.c.myid]).scalar_subquery() self.assert_compile( select([table2, s]), "SELECT myothertable.otherid, " @@ -922,7 +899,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): "- :param_1 AS anon_1", ) self.assert_compile( - select([select([table1.c.name]).as_scalar() + literal("x")]), + select([select([table1.c.name]).scalar_subquery() + literal("x")]), "SELECT (SELECT mytable.name FROM mytable) " "|| :param_1 AS anon_1", ) @@ -939,7 +916,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): # scalar selects should not have any attributes on their 'c' or # 'columns' attribute - s = select([table1.c.myid]).as_scalar() + s = select([table1.c.myid]).scalar_subquery() try: s.c.foo except exc.InvalidRequestError as err: @@ -965,12 +942,12 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): qlat = ( select([zips.c.latitude], zips.c.zipcode == zipcode) .correlate(None) - .as_scalar() + .scalar_subquery() ) qlng = ( select([zips.c.longitude], zips.c.zipcode == zipcode) .correlate(None) - .as_scalar() + .scalar_subquery() ) q = select( @@ -999,10 +976,10 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): zalias = zips.alias("main_zip") qlat = select( [zips.c.latitude], zips.c.zipcode == zalias.c.zipcode - ).as_scalar() + ).scalar_subquery() qlng = select( [zips.c.longitude], zips.c.zipcode == zalias.c.zipcode - ).as_scalar() + ).scalar_subquery() q = select( [ places.c.id, @@ -1025,7 +1002,9 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): ) a1 = table2.alias("t2alias") - s1 = select([a1.c.otherid], table1.c.myid == a1.c.otherid).as_scalar() + s1 = select( + [a1.c.otherid], table1.c.myid == a1.c.otherid + ).scalar_subquery() j1 = table1.join(table2, table1.c.myid == table2.c.otherid) s2 = select([table1, s1], from_obj=j1) self.assert_compile( @@ -2337,7 +2316,11 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): assert s3.compile().params == {"myid": 9, "myotherid": 7} # test using same 'unique' param object twice in one compile - s = select([table1.c.myid]).where(table1.c.myid == 12).as_scalar() + s = ( + select([table1.c.myid]) + .where(table1.c.myid == 12) + .scalar_subquery() + ) s2 = select([table1, s], table1.c.myid == s) self.assert_compile( s2, @@ -2884,7 +2867,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): lambda: sel1.c, ) - # calling label or as_scalar doesn't compile + # calling label or scalar_subquery doesn't compile # anything. sel2 = select([func.substr(my_str, 2, 3)]).label("my_substr") @@ -2895,7 +2878,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): dialect=default.DefaultDialect(), ) - sel3 = select([my_str]).as_scalar() + sel3 = select([my_str]).scalar_subquery() assert_raises_message( exc.CompileError, "Cannot compile Column object until its 'name' is assigned.", @@ -3919,13 +3902,13 @@ class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL): def test_correlate_semiauto_where(self): t1, t2, s1 = self._fixture() self._assert_where_correlated( - select([t2]).where(t2.c.a == s1.correlate(t2)) + select([t2]).where(t2.c.a == s1.correlate(t2).scalar_subquery()) ) def test_correlate_semiauto_column(self): t1, t2, s1 = self._fixture() self._assert_column_correlated( - select([t2, s1.correlate(t2).as_scalar()]) + select([t2, s1.correlate(t2).scalar_subquery()]) ) def test_correlate_semiauto_from(self): @@ -3935,31 +3918,35 @@ class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL): def test_correlate_semiauto_having(self): t1, t2, s1 = self._fixture() self._assert_having_correlated( - select([t2]).having(t2.c.a == s1.correlate(t2)) + select([t2]).having(t2.c.a == s1.correlate(t2).scalar_subquery()) ) def test_correlate_except_inclusion_where(self): t1, t2, s1 = self._fixture() self._assert_where_correlated( - select([t2]).where(t2.c.a == s1.correlate_except(t1)) + select([t2]).where( + t2.c.a == s1.correlate_except(t1).scalar_subquery() + ) ) def test_correlate_except_exclusion_where(self): t1, t2, s1 = self._fixture() self._assert_where_uncorrelated( - select([t2]).where(t2.c.a == s1.correlate_except(t2)) + select([t2]).where( + t2.c.a == s1.correlate_except(t2).scalar_subquery() + ) ) def test_correlate_except_inclusion_column(self): t1, t2, s1 = self._fixture() self._assert_column_correlated( - select([t2, s1.correlate_except(t1).as_scalar()]) + select([t2, s1.correlate_except(t1).scalar_subquery()]) ) def test_correlate_except_exclusion_column(self): t1, t2, s1 = self._fixture() self._assert_column_uncorrelated( - select([t2, s1.correlate_except(t2).as_scalar()]) + select([t2, s1.correlate_except(t2).scalar_subquery()]) ) def test_correlate_except_inclusion_from(self): @@ -3977,22 +3964,28 @@ class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL): def test_correlate_except_none(self): t1, t2, s1 = self._fixture() self._assert_where_all_correlated( - select([t1, t2]).where(t2.c.a == s1.correlate_except(None)) + select([t1, t2]).where( + t2.c.a == s1.correlate_except(None).scalar_subquery() + ) ) def test_correlate_except_having(self): t1, t2, s1 = self._fixture() self._assert_having_correlated( - select([t2]).having(t2.c.a == s1.correlate_except(t1)) + select([t2]).having( + t2.c.a == s1.correlate_except(t1).scalar_subquery() + ) ) def test_correlate_auto_where(self): t1, t2, s1 = self._fixture() - self._assert_where_correlated(select([t2]).where(t2.c.a == s1)) + self._assert_where_correlated( + select([t2]).where(t2.c.a == s1.scalar_subquery()) + ) def test_correlate_auto_column(self): t1, t2, s1 = self._fixture() - self._assert_column_correlated(select([t2, s1.as_scalar()])) + self._assert_column_correlated(select([t2, s1.scalar_subquery()])) def test_correlate_auto_from(self): t1, t2, s1 = self._fixture() @@ -4000,18 +3993,20 @@ class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL): def test_correlate_auto_having(self): t1, t2, s1 = self._fixture() - self._assert_having_correlated(select([t2]).having(t2.c.a == s1)) + self._assert_having_correlated( + select([t2]).having(t2.c.a == s1.scalar_subquery()) + ) def test_correlate_disabled_where(self): t1, t2, s1 = self._fixture() self._assert_where_uncorrelated( - select([t2]).where(t2.c.a == s1.correlate(None)) + select([t2]).where(t2.c.a == s1.correlate(None).scalar_subquery()) ) def test_correlate_disabled_column(self): t1, t2, s1 = self._fixture() self._assert_column_uncorrelated( - select([t2, s1.correlate(None).as_scalar()]) + select([t2, s1.correlate(None).scalar_subquery()]) ) def test_correlate_disabled_from(self): @@ -4023,19 +4018,21 @@ class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL): def test_correlate_disabled_having(self): t1, t2, s1 = self._fixture() self._assert_having_uncorrelated( - select([t2]).having(t2.c.a == s1.correlate(None)) + select([t2]).having(t2.c.a == s1.correlate(None).scalar_subquery()) ) def test_correlate_all_where(self): t1, t2, s1 = self._fixture() self._assert_where_all_correlated( - select([t1, t2]).where(t2.c.a == s1.correlate(t1, t2)) + select([t1, t2]).where( + t2.c.a == s1.correlate(t1, t2).scalar_subquery() + ) ) def test_correlate_all_column(self): t1, t2, s1 = self._fixture() self._assert_column_all_correlated( - select([t1, t2, s1.correlate(t1, t2).as_scalar()]) + select([t1, t2, s1.correlate(t1, t2).scalar_subquery()]) ) def test_correlate_all_from(self): @@ -4049,7 +4046,7 @@ class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL): assert_raises_message( exc.InvalidRequestError, "returned no FROM clauses due to auto-correlation", - select([t1, t2]).where(t2.c.a == s1).compile, + select([t1, t2]).where(t2.c.a == s1.scalar_subquery()).compile, ) def test_correlate_from_all_ok(self): @@ -4063,7 +4060,7 @@ class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL): def test_correlate_auto_where_singlefrom(self): t1, t2, s1 = self._fixture() s = select([t1.c.a]) - s2 = select([t1]).where(t1.c.a == s) + s2 = select([t1]).where(t1.c.a == s.scalar_subquery()) self.assert_compile( s2, "SELECT t1.a FROM t1 WHERE t1.a = " "(SELECT t1.a FROM t1)" ) @@ -4073,7 +4070,7 @@ class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL): s = select([t1.c.a]) - s2 = select([t1]).where(t1.c.a == s.correlate(t1)) + s2 = select([t1]).where(t1.c.a == s.correlate(t1).scalar_subquery()) self._assert_where_single_full_correlated(s2) def test_correlate_except_semiauto_where_singlefrom(self): @@ -4081,7 +4078,9 @@ class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL): s = select([t1.c.a]) - s2 = select([t1]).where(t1.c.a == s.correlate_except(t2)) + s2 = select([t1]).where( + t1.c.a == s.correlate_except(t2).scalar_subquery() + ) self._assert_where_single_full_correlated(s2) def test_correlate_alone_noeffect(self): @@ -4098,7 +4097,7 @@ class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL): s = select([t2.c.b]).where(t1.c.a == t2.c.a) s = s.correlate_except(t2).alias("s") - s2 = select([func.foo(s.c.b)]).as_scalar() + s2 = select([func.foo(s.c.b)]).scalar_subquery() s3 = select([t1], order_by=s2) self.assert_compile( @@ -4155,8 +4154,8 @@ class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL): t3 = table("t3", column("z")) s = select([t1.c.x]).where(t1.c.x == t2.c.y) - s2 = select([t3.c.z]).where(t3.c.z == s.as_scalar()) - s3 = select([t1]).where(t1.c.x == s2.as_scalar()) + s2 = select([t3.c.z]).where(t3.c.z == s.scalar_subquery()) + s3 = select([t1]).where(t1.c.x == s2.scalar_subquery()) self.assert_compile( s3, @@ -4214,15 +4213,6 @@ class CoercionTest(fixtures.TestBase, AssertsCompiledSQL): dialect=default.DefaultDialect(supports_native_boolean=False), ) - def test_null_constant(self): - self.assert_compile(_literal_as_text(None), "NULL") - - def test_false_constant(self): - self.assert_compile(_literal_as_text(False), "false") - - def test_true_constant(self): - self.assert_compile(_literal_as_text(True), "true") - def test_val_and_false(self): t = self._fixture() self.assert_compile(and_(t.c.id == 1, False), "false") diff --git a/test/sql/test_cte.py b/test/sql/test_cte.py index 7008bc1cc..ac46e7d5d 100644 --- a/test/sql/test_cte.py +++ b/test/sql/test_cte.py @@ -47,7 +47,9 @@ class CTETest(fixtures.TestBase, AssertsCompiledSQL): select([regional_sales.c.region]) .where( regional_sales.c.total_sales - > select([func.sum(regional_sales.c.total_sales) / 10]) + > select( + [func.sum(regional_sales.c.total_sales) / 10] + ).scalar_subquery() ) .cte("top_regions") ) diff --git a/test/sql/test_defaults.py b/test/sql/test_defaults.py index 76ef38e1f..ed7af2572 100644 --- a/test/sql/test_defaults.py +++ b/test/sql/test_defaults.py @@ -657,8 +657,8 @@ class DefaultTest(fixtures.TestBase): ): assert_raises_message( sa.exc.ArgumentError, - "SQL expression object expected, got object of type " - "<.* 'list'> instead", + r"SQL expression for WHERE/HAVING role expected, " + r"got \[(?:Sequence|ColumnDefault|DefaultClause)\('y'.*\)\]", t.select, [const], ) @@ -913,7 +913,7 @@ class PKDefaultTest(fixtures.TablesTest): "id", Integer, primary_key=True, - default=sa.select([func.max(t2.c.nextid)]).as_scalar(), + default=sa.select([func.max(t2.c.nextid)]).scalar_subquery(), ), Column("data", String(30)), ) @@ -1740,7 +1740,7 @@ class SpecialTypePKTest(fixtures.TestBase): self._run_test(server_default="1", autoincrement=False) def test_clause(self): - stmt = select([cast("INT_1", type_=self.MyInteger)]).as_scalar() + stmt = select([cast("INT_1", type_=self.MyInteger)]).scalar_subquery() self._run_test(default=stmt) @testing.requires.returning diff --git a/test/sql/test_delete.py b/test/sql/test_delete.py index f572a510c..1f4c49c56 100644 --- a/test/sql/test_delete.py +++ b/test/sql/test_delete.py @@ -109,13 +109,13 @@ class DeleteTest(_DeleteTestBase, fixtures.TablesTest, AssertsCompiledSQL): stmt, "DELETE FROM mytable AS t1 WHERE t1.myid = :myid_1" ) - def test_correlated(self): + def test_non_correlated_select(self): table1, table2 = self.tables.mytable, self.tables.myothertable # test a non-correlated WHERE clause s = select([table2.c.othername], table2.c.otherid == 7) self.assert_compile( - delete(table1, table1.c.name == s), + delete(table1, table1.c.name == s.scalar_subquery()), "DELETE FROM mytable " "WHERE mytable.name = (" "SELECT myothertable.othername " @@ -124,10 +124,13 @@ class DeleteTest(_DeleteTestBase, fixtures.TablesTest, AssertsCompiledSQL): ")", ) + def test_correlated_select(self): + table1, table2 = self.tables.mytable, self.tables.myothertable + # test one that is actually correlated... s = select([table2.c.othername], table2.c.otherid == table1.c.myid) self.assert_compile( - table1.delete(table1.c.name == s), + table1.delete(table1.c.name == s.scalar_subquery()), "DELETE FROM mytable " "WHERE mytable.name = (" "SELECT myothertable.othername " diff --git a/test/sql/test_deprecations.py b/test/sql/test_deprecations.py index 7990cd56c..8e8591aec 100644 --- a/test/sql/test_deprecations.py +++ b/test/sql/test_deprecations.py @@ -6,6 +6,7 @@ from sqlalchemy import column from sqlalchemy import create_engine from sqlalchemy import exc from sqlalchemy import ForeignKey +from sqlalchemy import func from sqlalchemy import Integer from sqlalchemy import MetaData from sqlalchemy import select @@ -17,6 +18,8 @@ from sqlalchemy import text from sqlalchemy import util from sqlalchemy.engine import default from sqlalchemy.schema import DDL +from sqlalchemy.sql import coercions +from sqlalchemy.sql import roles from sqlalchemy.sql import util as sql_util from sqlalchemy.testing import assert_raises from sqlalchemy.testing import assert_raises_message @@ -24,6 +27,7 @@ from sqlalchemy.testing import AssertsCompiledSQL from sqlalchemy.testing import engines from sqlalchemy.testing import eq_ from sqlalchemy.testing import fixtures +from sqlalchemy.testing import is_true from sqlalchemy.testing import mock @@ -372,6 +376,117 @@ class ForUpdateTest(fixtures.TestBase, AssertsCompiledSQL): eq_(s._for_update_arg.nowait, True) +class SubqueryCoercionsTest(fixtures.TestBase, AssertsCompiledSQL): + def test_column_roles(self): + stmt = select([table1.c.myid]) + + for role in [ + roles.WhereHavingRole, + roles.ExpressionElementRole, + roles.ByOfRole, + roles.OrderByRole, + # roles.LabeledColumnExprRole + ]: + with testing.expect_deprecated( + "coercing SELECT object to scalar " + "subquery in a column-expression context is deprecated" + ): + coerced = coercions.expect(role, stmt) + is_true(coerced.compare(stmt.scalar_subquery())) + + with testing.expect_deprecated( + "coercing SELECT object to scalar " + "subquery in a column-expression context is deprecated" + ): + coerced = coercions.expect(role, stmt.alias()) + is_true(coerced.compare(stmt.scalar_subquery())) + + def test_labeled_role(self): + stmt = select([table1.c.myid]) + + with testing.expect_deprecated( + "coercing SELECT object to scalar " + "subquery in a column-expression context is deprecated" + ): + coerced = coercions.expect(roles.LabeledColumnExprRole, stmt) + is_true(coerced.compare(stmt.scalar_subquery().label(None))) + + with testing.expect_deprecated( + "coercing SELECT object to scalar " + "subquery in a column-expression context is deprecated" + ): + coerced = coercions.expect( + roles.LabeledColumnExprRole, stmt.alias() + ) + is_true(coerced.compare(stmt.scalar_subquery().label(None))) + + def test_scalar_select(self): + + with testing.expect_deprecated( + "coercing SELECT object to scalar " + "subquery in a column-expression context is deprecated" + ): + self.assert_compile( + func.coalesce(select([table1.c.myid])), + "coalesce((SELECT mytable.myid FROM mytable))", + ) + + with testing.expect_deprecated( + "coercing SELECT object to scalar " + "subquery in a column-expression context is deprecated" + ): + s = select([table1.c.myid]).alias() + self.assert_compile( + select([table1.c.myid]).where(table1.c.myid == s), + "SELECT mytable.myid FROM mytable WHERE " + "mytable.myid = (SELECT mytable.myid FROM " + "mytable)", + ) + + with testing.expect_deprecated( + "coercing SELECT object to scalar " + "subquery in a column-expression context is deprecated" + ): + self.assert_compile( + select([table1.c.myid]).where(s > table1.c.myid), + "SELECT mytable.myid FROM mytable WHERE " + "mytable.myid < (SELECT mytable.myid FROM " + "mytable)", + ) + + with testing.expect_deprecated( + "coercing SELECT object to scalar " + "subquery in a column-expression context is deprecated" + ): + s = select([table1.c.myid]).alias() + self.assert_compile( + select([table1.c.myid]).where(table1.c.myid == s), + "SELECT mytable.myid FROM mytable WHERE " + "mytable.myid = (SELECT mytable.myid FROM " + "mytable)", + ) + + with testing.expect_deprecated( + "coercing SELECT object to scalar " + "subquery in a column-expression context is deprecated" + ): + self.assert_compile( + select([table1.c.myid]).where(s > table1.c.myid), + "SELECT mytable.myid FROM mytable WHERE " + "mytable.myid < (SELECT mytable.myid FROM " + "mytable)", + ) + + def test_as_scalar(self): + with testing.expect_deprecated( + r"The SelectBase.as_scalar\(\) method is deprecated and " + "will be removed in a future release." + ): + stmt = select([table1.c.myid]).as_scalar() + + is_true(stmt.compare(select([table1.c.myid]).scalar_subquery())) + + class TextTest(fixtures.TestBase, AssertsCompiledSQL): __dialect__ = "default" @@ -425,3 +540,11 @@ class TextTest(fixtures.TestBase, AssertsCompiledSQL): "The text.autocommit parameter is deprecated" ): t = text("select id, name from user", autocommit=True) + + +table1 = table( + "mytable", + column("myid", Integer), + column("name", String), + column("description", String), +) diff --git a/test/sql/test_generative.py b/test/sql/test_generative.py index 9b902e8ff..da139d7c0 100644 --- a/test/sql/test_generative.py +++ b/test/sql/test_generative.py @@ -22,7 +22,7 @@ from sqlalchemy.sql import operators from sqlalchemy.sql import table from sqlalchemy.sql import util as sql_util from sqlalchemy.sql import visitors -from sqlalchemy.sql.expression import _clone +from sqlalchemy.sql.elements import _clone from sqlalchemy.sql.expression import _from_objects from sqlalchemy.sql.visitors import ClauseVisitor from sqlalchemy.sql.visitors import cloned_traverse @@ -306,7 +306,7 @@ class BinaryEndpointTraversalTest(fixtures.TestBase): def test_subquery(self): a, b, c = column("a"), column("b"), column("c") - subq = select([c]).where(c == a).as_scalar() + subq = select([c]).where(c == a).scalar_subquery() expr = and_(a == b, b == subq) self._assert_traversal( expr, [(operators.eq, a, b), (operators.eq, b, subq)] @@ -706,7 +706,9 @@ class ClauseTest(fixtures.TestBase, AssertsCompiledSQL): select.append_whereclause(t1.c.col2 == 7) self.assert_compile( - select([t2]).where(t2.c.col1 == Vis().traverse(s)), + select([t2]).where( + t2.c.col1 == Vis().traverse(s).scalar_subquery() + ), "SELECT table2.col1, table2.col2, table2.col3 " "FROM table2 WHERE table2.col1 = " "(SELECT * FROM table1 WHERE table1.col1 = table2.col1 " @@ -739,7 +741,7 @@ class ClauseTest(fixtures.TestBase, AssertsCompiledSQL): t1a = t1.alias() s = select([1], t1.c.col1 == t1a.c.col1, from_obj=t1a).correlate(t1a) - s = select([t1]).where(t1.c.col1 == s) + s = select([t1]).where(t1.c.col1 == s.scalar_subquery()) self.assert_compile( s, "SELECT table1.col1, table1.col2, table1.col3 FROM table1 " @@ -760,7 +762,7 @@ class ClauseTest(fixtures.TestBase, AssertsCompiledSQL): s = select([t1]).where(t1.c.col1 == "foo").alias() s2 = select([1], t1.c.col1 == s.c.col1, from_obj=s).correlate(t1) - s3 = select([t1]).where(t1.c.col1 == s2) + s3 = select([t1]).where(t1.c.col1 == s2.scalar_subquery()) self.assert_compile( s3, "SELECT table1.col1, table1.col2, table1.col3 " @@ -982,7 +984,7 @@ class ClauseAdapterTest(fixtures.TestBase, AssertsCompiledSQL): s = select( [literal_column("*")], from_obj=[t1alias, t2alias] - ).as_scalar() + ).scalar_subquery() assert t2alias in s._froms assert t1alias in s._froms @@ -1011,7 +1013,7 @@ class ClauseAdapterTest(fixtures.TestBase, AssertsCompiledSQL): s = ( select([literal_column("*")], from_obj=[t1alias, t2alias]) .correlate(t2alias) - .as_scalar() + .scalar_subquery() ) self.assert_compile( select([literal_column("*")], t2alias.c.col1 == s), @@ -1037,7 +1039,7 @@ class ClauseAdapterTest(fixtures.TestBase, AssertsCompiledSQL): s = ( select([literal_column("*")]) .where(t1.c.col1 == t2.c.col1) - .as_scalar() + .scalar_subquery() ) self.assert_compile( select([t1.c.col1, s]), @@ -1064,7 +1066,7 @@ class ClauseAdapterTest(fixtures.TestBase, AssertsCompiledSQL): select([literal_column("*")]) .where(t1.c.col1 == t2.c.col1) .correlate(t1) - .as_scalar() + .scalar_subquery() ) self.assert_compile( select([t1.c.col1, s]), @@ -1102,7 +1104,7 @@ class ClauseAdapterTest(fixtures.TestBase, AssertsCompiledSQL): select([t2.c.col1]) .where(t2.c.col1 == t1.c.col1) .correlate(t2) - .as_scalar() + .scalar_subquery() ) # test subquery - given only t1 and t2 in the enclosing selectable, @@ -1112,7 +1114,7 @@ class ClauseAdapterTest(fixtures.TestBase, AssertsCompiledSQL): select([t2.c.col1]) .where(t2.c.col1 == t1.c.col1) .correlate_except(t1) - .as_scalar() + .scalar_subquery() ) # use both subqueries in statements @@ -1257,7 +1259,9 @@ class ClauseAdapterTest(fixtures.TestBase, AssertsCompiledSQL): [literal_column("*")], t1.c.col1 == t2.c.col2, from_obj=[t1, t2], - ).correlate(t1) + ) + .correlate(t1) + .scalar_subquery() ) ), "SELECT t1alias.col1, t1alias.col2, t1alias.col3, " @@ -1277,7 +1281,9 @@ class ClauseAdapterTest(fixtures.TestBase, AssertsCompiledSQL): [literal_column("*")], t1.c.col1 == t2.c.col2, from_obj=[t1, t2], - ).correlate(t2) + ) + .correlate(t2) + .scalar_subquery() ) ), "SELECT t1alias.col1, t1alias.col2, t1alias.col3, " @@ -1381,9 +1387,9 @@ class ClauseAdapterTest(fixtures.TestBase, AssertsCompiledSQL): select([t1alias, t2alias]).where( t1alias.c.col1 == vis.traverse( - select( - ["*"], t1.c.col1 == t2.c.col2, from_obj=[t1, t2] - ).correlate(t1) + select(["*"], t1.c.col1 == t2.c.col2, from_obj=[t1, t2]) + .correlate(t1) + .scalar_subquery() ) ), "SELECT t1alias.col1, t1alias.col2, t1alias.col3, " @@ -1403,9 +1409,9 @@ class ClauseAdapterTest(fixtures.TestBase, AssertsCompiledSQL): t2alias.select().where( t2alias.c.col2 == vis.traverse( - select( - ["*"], t1.c.col1 == t2.c.col2, from_obj=[t1, t2] - ).correlate(t2) + select(["*"], t1.c.col1 == t2.c.col2, from_obj=[t1, t2]) + .correlate(t2) + .scalar_subquery() ) ), "SELECT t2alias.col1, t2alias.col2, t2alias.col3 " diff --git a/test/sql/test_inspect.py b/test/sql/test_inspect.py index 0b7aa7a55..d2a2c1c48 100644 --- a/test/sql/test_inspect.py +++ b/test/sql/test_inspect.py @@ -5,6 +5,7 @@ from sqlalchemy import inspect from sqlalchemy import Integer from sqlalchemy import MetaData from sqlalchemy import Table +from sqlalchemy.sql import ClauseElement from sqlalchemy.testing import fixtures from sqlalchemy.testing import is_ @@ -37,5 +38,13 @@ class TestCoreInspection(fixtures.TestBase): # absence of __clause_element__ as a test for "this is the clause # element" must be maintained + class Foo(ClauseElement): + pass + + assert not hasattr(Foo(), "__clause_element__") + + def test_col_now_has_a_clauseelement(self): + x = Column("foo", Integer) - assert not hasattr(x, "__clause_element__") + + assert hasattr(x, "__clause_element__") diff --git a/test/sql/test_join_rewriting.py b/test/sql/test_join_rewriting.py index e91557cd1..b9bcfc16a 100644 --- a/test/sql/test_join_rewriting.py +++ b/test/sql/test_join_rewriting.py @@ -259,8 +259,8 @@ class _JoinRewriteTestBase(AssertsCompiledSQL): self._test(s, self._f_b1a_where_in_b2a) def test_anon_scalar_subqueries(self): - s1 = select([1]).as_scalar() - s2 = select([2]).as_scalar() + s1 = select([1]).scalar_subquery() + s2 = select([2]).scalar_subquery() s = select([s1, s2]).apply_labels() self._test(s, self._anon_scalar_subqueries) diff --git a/test/sql/test_metadata.py b/test/sql/test_metadata.py index 3d60fb60e..3f9667609 100644 --- a/test/sql/test_metadata.py +++ b/test/sql/test_metadata.py @@ -31,7 +31,6 @@ from sqlalchemy import Unicode from sqlalchemy import UniqueConstraint from sqlalchemy import util from sqlalchemy.engine import default -from sqlalchemy.sql import elements from sqlalchemy.sql import naming from sqlalchemy.testing import assert_raises from sqlalchemy.testing import assert_raises_message @@ -3378,7 +3377,8 @@ class ConstraintTest(fixtures.TestBase): assert_raises_message( exc.ArgumentError, - r"Element Table\('t2', .* is not a string name or column element", + r"String column name or column object for DDL constraint " + r"expected, got .*SomeClass", Index, "foo", SomeClass(), @@ -4609,7 +4609,7 @@ class NamingConventionTest(fixtures.TestBase, AssertsCompiledSQL): u1 = self._fixture( naming_convention={"ck": "ck_%(table_name)s_%(constraint_name)s"} ) - ck = CheckConstraint(u1.c.data == "x", name=elements._defer_name(None)) + ck = CheckConstraint(u1.c.data == "x", name=naming._defer_name(None)) assert_raises_message( exc.InvalidRequestError, diff --git a/test/sql/test_operators.py b/test/sql/test_operators.py index c6eff6ac9..f85a601ba 100644 --- a/test/sql/test_operators.py +++ b/test/sql/test_operators.py @@ -26,6 +26,7 @@ from sqlalchemy.schema import Table from sqlalchemy.sql import all_ from sqlalchemy.sql import any_ from sqlalchemy.sql import asc +from sqlalchemy.sql import coercions from sqlalchemy.sql import collate from sqlalchemy.sql import column from sqlalchemy.sql import compiler @@ -34,10 +35,10 @@ from sqlalchemy.sql import false from sqlalchemy.sql import literal from sqlalchemy.sql import null from sqlalchemy.sql import operators +from sqlalchemy.sql import roles from sqlalchemy.sql import sqltypes from sqlalchemy.sql import table from sqlalchemy.sql import true -from sqlalchemy.sql.elements import _literal_as_text from sqlalchemy.sql.elements import BindParameter from sqlalchemy.sql.elements import Label from sqlalchemy.sql.expression import BinaryExpression @@ -82,13 +83,17 @@ class DefaultColumnComparatorTest(fixtures.TestBase): assert left.comparator.operate(operator, right).compare( BinaryExpression( - _literal_as_text(left), _literal_as_text(right), operator + coercions.expect(roles.WhereHavingRole, left), + coercions.expect(roles.WhereHavingRole, right), + operator, ) ) assert operator(left, right).compare( BinaryExpression( - _literal_as_text(left), _literal_as_text(right), operator + coercions.expect(roles.WhereHavingRole, left), + coercions.expect(roles.WhereHavingRole, right), + operator, ) ) @@ -227,8 +232,9 @@ class DefaultColumnComparatorTest(fixtures.TestBase): left = column("left") foo = ClauseList() assert_raises_message( - exc.InvalidRequestError, - r"in_\(\) accepts either a list of expressions, a selectable", + exc.ArgumentError, + r"IN expression list, SELECT construct, or bound parameter " + r"object expected, got .*ClauseList", left.in_, [foo], ) @@ -237,8 +243,9 @@ class DefaultColumnComparatorTest(fixtures.TestBase): left = column("left") right = column("right") assert_raises_message( - exc.InvalidRequestError, - r"in_\(\) accepts either a list of expressions, a selectable", + exc.ArgumentError, + r"IN expression list, SELECT construct, or bound parameter " + r"object expected, got .*ColumnClause", left.in_, right, ) @@ -253,8 +260,9 @@ class DefaultColumnComparatorTest(fixtures.TestBase): left = column("left") right = column("right", HasGetitem) assert_raises_message( - exc.InvalidRequestError, - r"in_\(\) accepts either a list of expressions, a selectable", + exc.ArgumentError, + r"IN expression list, SELECT construct, or bound parameter " + r"object expected, got .*ColumnClause", left.in_, right, ) @@ -1680,7 +1688,7 @@ class InTest(fixtures.TestBase, testing.AssertsCompiledSQL): select( [ self.table1.c.myid.in_( - select([self.table2.c.otherid]).as_scalar() + select([self.table2.c.otherid]).scalar_subquery() ) ] ), @@ -1738,6 +1746,29 @@ class InTest(fixtures.TestBase, testing.AssertsCompiledSQL): self.table1.c.myid.in_([None]), "mytable.myid IN (NULL)" ) + def test_in_set(self): + self.assert_compile( + self.table1.c.myid.in_({1, 2, 3}), + "mytable.myid IN (:myid_1, :myid_2, :myid_3)", + ) + + def test_in_arbitrary_sequence(self): + class MySeq(object): + def __init__(self, d): + self.d = d + + def __getitem__(self, idx): + return self.d[idx] + + def __iter__(self): + return iter(self.d) + + seq = MySeq([1, 2, 3]) + self.assert_compile( + self.table1.c.myid.in_(seq), + "mytable.myid IN (:myid_1, :myid_2, :myid_3)", + ) + def test_empty_in_dynamic_1(self): self.assert_compile( self.table1.c.myid.in_([]), @@ -2073,7 +2104,9 @@ class NegationTest(fixtures.TestBase, testing.AssertsCompiledSQL): assert not (self.table1.c.myid + 5)._is_implicitly_boolean assert not not_(column("x", Boolean))._is_implicitly_boolean assert ( - not select([self.table1.c.myid]).as_scalar()._is_implicitly_boolean + not select([self.table1.c.myid]) + .scalar_subquery() + ._is_implicitly_boolean ) assert not text("x = y")._is_implicitly_boolean assert not literal_column("x = y")._is_implicitly_boolean @@ -2869,7 +2902,8 @@ class AnyAllTest(fixtures.TestBase, testing.AssertsCompiledSQL): t = self._fixture() self.assert_compile( - 5 == any_(select([t.c.data]).where(t.c.data < 10)), + 5 + == any_(select([t.c.data]).where(t.c.data < 10).scalar_subquery()), ":param_1 = ANY (SELECT tab1.data " "FROM tab1 WHERE tab1.data < :data_1)", checkparams={"data_1": 10, "param_1": 5}, @@ -2879,7 +2913,11 @@ class AnyAllTest(fixtures.TestBase, testing.AssertsCompiledSQL): t = self._fixture() self.assert_compile( - 5 == select([t.c.data]).where(t.c.data < 10).as_scalar().any_(), + 5 + == select([t.c.data]) + .where(t.c.data < 10) + .scalar_subquery() + .any_(), ":param_1 = ANY (SELECT tab1.data " "FROM tab1 WHERE tab1.data < :data_1)", checkparams={"data_1": 10, "param_1": 5}, @@ -2889,7 +2927,8 @@ class AnyAllTest(fixtures.TestBase, testing.AssertsCompiledSQL): t = self._fixture() self.assert_compile( - 5 == all_(select([t.c.data]).where(t.c.data < 10)), + 5 + == all_(select([t.c.data]).where(t.c.data < 10).scalar_subquery()), ":param_1 = ALL (SELECT tab1.data " "FROM tab1 WHERE tab1.data < :data_1)", checkparams={"data_1": 10, "param_1": 5}, @@ -2899,7 +2938,11 @@ class AnyAllTest(fixtures.TestBase, testing.AssertsCompiledSQL): t = self._fixture() self.assert_compile( - 5 == select([t.c.data]).where(t.c.data < 10).as_scalar().all_(), + 5 + == select([t.c.data]) + .where(t.c.data < 10) + .scalar_subquery() + .all_(), ":param_1 = ALL (SELECT tab1.data " "FROM tab1 WHERE tab1.data < :data_1)", checkparams={"data_1": 10, "param_1": 5}, diff --git a/test/sql/test_resultset.py b/test/sql/test_resultset.py index 5987c7746..6e48374ca 100644 --- a/test/sql/test_resultset.py +++ b/test/sql/test_resultset.py @@ -121,7 +121,7 @@ class ResultProxyTest(fixtures.TablesTest): sel = ( select([users.c.user_id]) .where(users.c.user_name == "jack") - .as_scalar() + .scalar_subquery() ) for row in select([sel + 1, sel + 3], bind=users.bind).execute(): eq_(row["anon_1"], 8) diff --git a/test/sql/test_returning.py b/test/sql/test_returning.py index aa10626c5..ad1eb3348 100644 --- a/test/sql/test_returning.py +++ b/test/sql/test_returning.py @@ -222,7 +222,7 @@ class CompositeStatementTest(fixtures.TestBase): stmt = ( t2.insert() - .values(x=select([t1.c.x]).as_scalar()) + .values(x=select([t1.c.x]).scalar_subquery()) .returning(t2.c.x) ) diff --git a/test/sql/test_roles.py b/test/sql/test_roles.py new file mode 100644 index 000000000..81934de24 --- /dev/null +++ b/test/sql/test_roles.py @@ -0,0 +1,218 @@ +from sqlalchemy import Column +from sqlalchemy import exc +from sqlalchemy import Integer +from sqlalchemy import MetaData +from sqlalchemy import select +from sqlalchemy import Table +from sqlalchemy import text +from sqlalchemy.schema import DDL +from sqlalchemy.schema import Sequence +from sqlalchemy.sql import ClauseElement +from sqlalchemy.sql import coercions +from sqlalchemy.sql import column +from sqlalchemy.sql import false +from sqlalchemy.sql import False_ +from sqlalchemy.sql import literal +from sqlalchemy.sql import roles +from sqlalchemy.sql import true +from sqlalchemy.sql import True_ +from sqlalchemy.sql.coercions import expect +from sqlalchemy.sql.elements import _truncated_label +from sqlalchemy.sql.elements import Null +from sqlalchemy.testing import assert_raises_message +from sqlalchemy.testing import fixtures +from sqlalchemy.testing import is_ +from sqlalchemy.testing import is_instance_of +from sqlalchemy.testing import is_true + +m = MetaData() + +t = Table("t", m, Column("q", Integer)) + + +class NotAThing1(object): + pass + + +not_a_thing1 = NotAThing1() + + +class NotAThing2(ClauseElement): + pass + + +not_a_thing2 = NotAThing2() + + +class NotAThing3(object): + def __clause_element__(self): + return not_a_thing2 + + +not_a_thing3 = NotAThing3() + + +class RoleTest(fixtures.TestBase): + # TODO: the individual role tests here are incomplete. The functionality + # of each role is covered by other tests in the sql testing suite however + # ideally they'd all have direct tests here as well. + + def _test_role_neg_comparisons(self, role): + impl = coercions._impl_lookup[role] + role_name = impl.name + + assert_raises_message( + exc.ArgumentError, + r"%s expected, got .*NotAThing1" % role_name, + expect, + role, + not_a_thing1, + ) + + assert_raises_message( + exc.ArgumentError, + r"%s expected, got .*NotAThing2" % role_name, + expect, + role, + not_a_thing2, + ) + + assert_raises_message( + exc.ArgumentError, + r"%s expected, got .*NotAThing3" % role_name, + expect, + role, + not_a_thing3, + ) + + assert_raises_message( + exc.ArgumentError, + r"%s expected for argument 'foo'; got .*NotAThing3" % role_name, + expect, + role, + not_a_thing3, + argname="foo", + ) + + def test_const_expr_role(self): + t = true() + is_(expect(roles.ConstExprRole, t), t) + + f = false() + is_(expect(roles.ConstExprRole, f), f) + + is_instance_of(expect(roles.ConstExprRole, True), True_) + + is_instance_of(expect(roles.ConstExprRole, False), False_) + + is_instance_of(expect(roles.ConstExprRole, None), Null) + + def test_truncated_label_role(self): + is_instance_of( + expect(roles.TruncatedLabelRole, "foobar"), _truncated_label + ) + + def test_labeled_column_expr_role(self): + c = column("q") + is_true(expect(roles.LabeledColumnExprRole, c).compare(c)) + + is_true( + expect(roles.LabeledColumnExprRole, c.label("foo")).compare( + c.label("foo") + ) + ) + + is_true( + expect( + roles.LabeledColumnExprRole, + select([column("q")]).scalar_subquery(), + ).compare(select([column("q")]).label(None)) + ) + + is_true( + expect(roles.LabeledColumnExprRole, not_a_thing1).compare( + literal(not_a_thing1).label(None) + ) + ) + + def test_scalar_select_no_coercion(self): + # this is also tested in test/sql/test_deprecations.py; when the + # deprecation is turned to an error, those tests go away, and these + # will assert the correct exception plus informative error message. + assert_raises_message( + exc.SADeprecationWarning, + "coercing SELECT object to scalar subquery in a column-expression " + "context is deprecated", + expect, + roles.LabeledColumnExprRole, + select([column("q")]), + ) + + assert_raises_message( + exc.SADeprecationWarning, + "coercing SELECT object to scalar subquery in a column-expression " + "context is deprecated", + expect, + roles.LabeledColumnExprRole, + select([column("q")]).alias(), + ) + + def test_statement_no_text_coercion(self): + assert_raises_message( + exc.ArgumentError, + r"Textual SQL expression 'select \* from table' should be " + r"explicitly declared", + expect, + roles.StatementRole, + "select * from table", + ) + + def test_statement_text_coercion(self): + is_true( + expect( + roles.CoerceTextStatementRole, "select * from table" + ).compare(text("select * from table")) + ) + + def test_select_statement_no_text_coercion(self): + assert_raises_message( + exc.ArgumentError, + r"Textual SQL expression 'select \* from table' should be " + r"explicitly declared", + expect, + roles.SelectStatementRole, + "select * from table", + ) + + def test_statement_coercion_select(self): + is_true( + expect(roles.CoerceTextStatementRole, select([t])).compare( + select([t]) + ) + ) + + def test_statement_coercion_ddl(self): + d1 = DDL("hi") + is_(expect(roles.CoerceTextStatementRole, d1), d1) + + def test_statement_coercion_sequence(self): + s1 = Sequence("hi") + is_(expect(roles.CoerceTextStatementRole, s1), s1) + + def test_columns_clause_role(self): + is_(expect(roles.ColumnsClauseRole, t.c.q), t.c.q) + + def test_truncated_label_role_neg(self): + self._test_role_neg_comparisons(roles.TruncatedLabelRole) + + def test_where_having_role_neg(self): + self._test_role_neg_comparisons(roles.WhereHavingRole) + + def test_by_of_role_neg(self): + self._test_role_neg_comparisons(roles.ByOfRole) + + def test_const_expr_role_neg(self): + self._test_role_neg_comparisons(roles.ConstExprRole) + + def test_columns_clause_role_neg(self): + self._test_role_neg_comparisons(roles.ColumnsClauseRole) diff --git a/test/sql/test_selectable.py b/test/sql/test_selectable.py index a4d3e1b40..f88243fc2 100644 --- a/test/sql/test_selectable.py +++ b/test/sql/test_selectable.py @@ -31,7 +31,6 @@ from sqlalchemy import util from sqlalchemy.sql import Alias from sqlalchemy.sql import column from sqlalchemy.sql import elements -from sqlalchemy.sql import expression from sqlalchemy.sql import table from sqlalchemy.sql import util as sql_util from sqlalchemy.sql import visitors @@ -221,7 +220,7 @@ class SelectableTest( s3c1 = s3._clone() eq_( - expression._cloned_intersection([s1c1, s3c1], [s2c1, s1c2]), + elements._cloned_intersection([s1c1, s3c1], [s2c1, s1c2]), set([s1c1]), ) @@ -240,7 +239,7 @@ class SelectableTest( s3c1 = s3._clone() eq_( - expression._cloned_difference([s1c1, s2c1, s3c1], [s2c1, s1c2]), + elements._cloned_difference([s1c1, s2c1, s3c1], [s2c1, s1c2]), set([s3c1]), ) @@ -313,7 +312,7 @@ class SelectableTest( assert sel3.corresponding_column(col) is sel3.c.foo def test_with_only_generative(self): - s1 = table1.select().as_scalar() + s1 = table1.select().scalar_subquery() self.assert_compile( s1.with_only_columns([s1]), "SELECT (SELECT table1.col1, table1.col2, " @@ -365,12 +364,18 @@ class SelectableTest( criterion = a.c.col1 == table2.c.col2 self.assert_(criterion.compare(j.onclause)) + @testing.fails("not supported with rework, need a new approach") def test_alias_handles_column_context(self): # not quite a use case yet but this is expected to become # prominent w/ PostgreSQL's tuple functions stmt = select([table1.c.col1, table1.c.col2]) a = stmt.alias("a") + + # TODO: this case is crazy, sending SELECT or FROMCLAUSE has to + # be figured out - is it a scalar row query? what kinds of + # statements go into functions in PG. seems likely select statment, + # but not alias, subquery or other FROM object self.assert_compile( select([func.foo(a)]), "SELECT foo(SELECT table1.col1, table1.col2 FROM table1) " @@ -652,7 +657,7 @@ class SelectableTest( self.assert_(criterion.compare(j.onclause)) def test_scalar_cloned_comparator(self): - sel = select([table1.c.col1]).as_scalar() + sel = select([table1.c.col1]).scalar_subquery() expr = sel == table1.c.col1 sel2 = visitors.ReplacingCloningVisitor().traverse(sel) @@ -2535,7 +2540,7 @@ class ResultMapTest(fixtures.TestBase): def test_column_subquery_plain(self): t = self._fixture() - s1 = select([t.c.x]).where(t.c.x > 5).as_scalar() + s1 = select([t.c.x]).where(t.c.x > 5).scalar_subquery() s2 = select([s1]) mapping = self._mapping(s2) assert t.c.x not in mapping diff --git a/test/sql/test_text.py b/test/sql/test_text.py index 188ac3878..afc775598 100644 --- a/test/sql/test_text.py +++ b/test/sql/test_text.py @@ -564,7 +564,7 @@ class AsFromTest(fixtures.TestBase, AssertsCompiledSQL): def test_scalar_subquery(self): t = text("select id from user").columns(id=Integer) - subq = t.as_scalar() + subq = t.scalar_subquery() assert subq.type._type_affinity is Integer()._type_affinity diff --git a/test/sql/test_types.py b/test/sql/test_types.py index a1b1f024b..a5c9313f8 100644 --- a/test/sql/test_types.py +++ b/test/sql/test_types.py @@ -2608,7 +2608,8 @@ class ExpressionTest( assert_raises_message( exc.ArgumentError, - r"Object some_sqla_thing\(\) is not legal as a SQL literal value", + r"SQL expression element or literal value expected, got " + r"some_sqla_thing\(\).", lambda: column("a", String) == SomeSQLAThing(), ) diff --git a/test/sql/test_update.py b/test/sql/test_update.py index 514076daa..9309ca45a 100644 --- a/test/sql/test_update.py +++ b/test/sql/test_update.py @@ -179,7 +179,7 @@ class UpdateTest(_UpdateFromTestBase, fixtures.TablesTest, AssertsCompiledSQL): # test a non-correlated WHERE clause s = select([table2.c.othername], table2.c.otherid == 7) - u = update(table1, table1.c.name == s) + u = update(table1, table1.c.name == s.scalar_subquery()) self.assert_compile( u, "UPDATE mytable SET myid=:myid, name=:name, " @@ -194,7 +194,7 @@ class UpdateTest(_UpdateFromTestBase, fixtures.TablesTest, AssertsCompiledSQL): # test one that is actually correlated... s = select([table2.c.othername], table2.c.otherid == table1.c.myid) - u = table1.update(table1.c.name == s) + u = table1.update(table1.c.name == s.scalar_subquery()) self.assert_compile( u, "UPDATE mytable SET myid=:myid, name=:name, " |