diff options
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, " |