mirror of
https://github.com/sqlalchemy/sqlalchemy.git
synced 2026-05-06 17:01:07 -04:00
Mike Bayer
9a0d004331
The :meth:`_sql.Select.filter_by`, :meth:`_sql.Update.filter_by` and :meth:`_sql.Delete.filter_by` methods now search across all entities present in the statement, rather than limiting their search to only the last joined entity or the first FROM entity. This allows these methods to locate attributes unambiguously across multiple joined tables, resolving issues where changing the order of operations such as :meth:`_sql.Select.with_only_columns` would cause the method to fail. If an attribute name exists in more than one FROM clause entity, an :class:`_exc.AmbiguousColumnError` is now raised, indicating that :meth:`_sql.Select.filter` (or :meth:`_sql.Select.where`) should be used instead with explicit table-qualified column references. Fixes: #8601 Change-Id: I6a46b8f4784801f95f7980ca8ef92f1947653572
733 lines
24 KiB
Python
733 lines
24 KiB
Python
from sqlalchemy import cast
|
|
from sqlalchemy import Column
|
|
from sqlalchemy import exc
|
|
from sqlalchemy import ForeignKey
|
|
from sqlalchemy import func
|
|
from sqlalchemy import Integer
|
|
from sqlalchemy import MetaData
|
|
from sqlalchemy import select
|
|
from sqlalchemy import String
|
|
from sqlalchemy import Table
|
|
from sqlalchemy import testing
|
|
from sqlalchemy import true
|
|
from sqlalchemy import tuple_
|
|
from sqlalchemy import union
|
|
from sqlalchemy.sql import column
|
|
from sqlalchemy.sql import literal
|
|
from sqlalchemy.sql import table
|
|
from sqlalchemy.testing import assert_raises_message
|
|
from sqlalchemy.testing import AssertsCompiledSQL
|
|
from sqlalchemy.testing import eq_
|
|
from sqlalchemy.testing import expect_raises_message
|
|
from sqlalchemy.testing import fixtures
|
|
from sqlalchemy.testing import is_
|
|
|
|
table1 = table(
|
|
"mytable",
|
|
column("myid", Integer),
|
|
column("name", String),
|
|
column("description", String),
|
|
)
|
|
|
|
table2 = table(
|
|
"myothertable", column("otherid", Integer), column("othername", String)
|
|
)
|
|
|
|
metadata = MetaData()
|
|
|
|
|
|
parent = Table(
|
|
"parent",
|
|
metadata,
|
|
Column("id", Integer, primary_key=True),
|
|
Column("data", String(50)),
|
|
)
|
|
child = Table(
|
|
"child",
|
|
metadata,
|
|
Column("id", Integer, primary_key=True),
|
|
Column("parent_id", ForeignKey("parent.id")),
|
|
Column("data", String(50)),
|
|
)
|
|
|
|
grandchild = Table(
|
|
"grandchild",
|
|
metadata,
|
|
Column("id", Integer, primary_key=True),
|
|
Column("child_id", ForeignKey("child.id")),
|
|
)
|
|
|
|
grandchild_w_parent = Table(
|
|
"grandchildwparent",
|
|
metadata,
|
|
Column("id", Integer, primary_key=True),
|
|
Column("parent_id", ForeignKey("parent.id")),
|
|
Column("child_id", ForeignKey("child.id")),
|
|
)
|
|
|
|
|
|
class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
|
|
__dialect__ = "default"
|
|
|
|
def test_old_bracket_style_fail(self):
|
|
with expect_raises_message(
|
|
exc.ArgumentError,
|
|
r"Column expression, FROM clause, or other columns clause .*"
|
|
r".*Did you mean to say",
|
|
):
|
|
select([table1.c.myid])
|
|
|
|
def test_new_calling_style(self):
|
|
stmt = select(table1.c.myid).where(table1.c.myid == table2.c.otherid)
|
|
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT mytable.myid FROM mytable, myothertable "
|
|
"WHERE mytable.myid = myothertable.otherid",
|
|
)
|
|
|
|
@testing.combinations(
|
|
(
|
|
lambda tbl: select().select_from(tbl).where(tbl.c.id == 123),
|
|
"SELECT FROM tbl WHERE tbl.id = :id_1",
|
|
),
|
|
(lambda tbl: select().where(true()), "SELECT WHERE 1 = 1"),
|
|
(
|
|
lambda tbl: select()
|
|
.select_from(tbl)
|
|
.where(tbl.c.id == 123)
|
|
.exists(),
|
|
"EXISTS (SELECT FROM tbl WHERE tbl.id = :id_1)",
|
|
),
|
|
)
|
|
def test_select_no_columns(self, stmt, expected):
|
|
"""test #9440"""
|
|
|
|
tbl = table("tbl", column("id"))
|
|
|
|
stmt = testing.resolve_lambda(stmt, tbl=tbl)
|
|
|
|
self.assert_compile(stmt, expected)
|
|
|
|
def test_new_calling_style_clauseelement_thing_that_has_iter(self):
|
|
class Thing:
|
|
def __clause_element__(self):
|
|
return table1
|
|
|
|
def __iter__(self):
|
|
return iter(["a", "b", "c"])
|
|
|
|
stmt = select(Thing())
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT mytable.myid, mytable.name, "
|
|
"mytable.description FROM mytable",
|
|
)
|
|
|
|
def test_new_calling_style_inspectable_ce_thing_that_has_iter(self):
|
|
class Thing:
|
|
def __iter__(self):
|
|
return iter(["a", "b", "c"])
|
|
|
|
class InspectedThing:
|
|
def __clause_element__(self):
|
|
return table1
|
|
|
|
from sqlalchemy.inspection import _inspects
|
|
|
|
@_inspects(Thing)
|
|
def _ce(thing):
|
|
return InspectedThing()
|
|
|
|
stmt = select(Thing())
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT mytable.myid, mytable.name, "
|
|
"mytable.description FROM mytable",
|
|
)
|
|
|
|
def test_join_nofrom_implicit_left_side_explicit_onclause(self):
|
|
stmt = select(table1).join(table2, table1.c.myid == table2.c.otherid)
|
|
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT mytable.myid, mytable.name, mytable.description "
|
|
"FROM mytable JOIN myothertable "
|
|
"ON mytable.myid = myothertable.otherid",
|
|
)
|
|
|
|
def test_join_nofrom_implicit_left_side_explicit_onclause_3level(self):
|
|
stmt = (
|
|
select(parent)
|
|
.join(child, child.c.parent_id == parent.c.id)
|
|
.join(grandchild, grandchild.c.child_id == child.c.id)
|
|
)
|
|
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT parent.id, parent.data FROM parent JOIN child "
|
|
"ON child.parent_id = parent.id "
|
|
"JOIN grandchild ON grandchild.child_id = child.id",
|
|
)
|
|
|
|
def test_join_nofrom_explicit_left_side_explicit_onclause(self):
|
|
stmt = select(table1).join_from(
|
|
table1, table2, table1.c.myid == table2.c.otherid
|
|
)
|
|
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT mytable.myid, mytable.name, mytable.description "
|
|
"FROM mytable JOIN myothertable "
|
|
"ON mytable.myid = myothertable.otherid",
|
|
)
|
|
|
|
@testing.variation(
|
|
"jointype",
|
|
["child_grandchild", "parent_grandchild", "grandchild_alone"],
|
|
)
|
|
def test_join_from_multiple_explicit_left_side_implicit_onclause(
|
|
self, jointype
|
|
):
|
|
"""test #12931
|
|
|
|
when join_from() is indicated, favor the explicit "left" side given
|
|
over the "left side of hte join" for creating onclause.
|
|
|
|
when join() is indicated, use the normal behavior of assuming
|
|
right side of the previous join is the new left side.
|
|
|
|
"""
|
|
|
|
if jointype.child_grandchild:
|
|
stmt = (
|
|
select(parent)
|
|
.join_from(parent, child)
|
|
.join_from(child, grandchild_w_parent)
|
|
)
|
|
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT parent.id, parent.data FROM parent JOIN "
|
|
"child ON parent.id = child.parent_id "
|
|
"JOIN grandchildwparent "
|
|
"ON child.id = grandchildwparent.child_id",
|
|
)
|
|
|
|
elif jointype.parent_grandchild:
|
|
stmt = (
|
|
select(parent)
|
|
.join_from(parent, child)
|
|
.join_from(parent, grandchild_w_parent)
|
|
)
|
|
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT parent.id, parent.data FROM parent "
|
|
"JOIN child ON parent.id = child.parent_id "
|
|
"JOIN grandchildwparent "
|
|
"ON parent.id = grandchildwparent.parent_id",
|
|
)
|
|
elif jointype.grandchild_alone:
|
|
stmt = (
|
|
select(parent)
|
|
.join_from(parent, child)
|
|
.join(grandchild_w_parent)
|
|
)
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT parent.id, parent.data FROM parent "
|
|
"JOIN child ON parent.id = child.parent_id "
|
|
"JOIN grandchildwparent "
|
|
"ON child.id = grandchildwparent.child_id",
|
|
)
|
|
else:
|
|
jointype.fail()
|
|
|
|
def test_outerjoin_nofrom_explicit_left_side_explicit_onclause(self):
|
|
stmt = select(table1).outerjoin_from(
|
|
table1, table2, table1.c.myid == table2.c.otherid
|
|
)
|
|
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT mytable.myid, mytable.name, mytable.description "
|
|
"FROM mytable LEFT OUTER JOIN myothertable "
|
|
"ON mytable.myid = myothertable.otherid",
|
|
)
|
|
|
|
def test_join_nofrom_implicit_left_side_implicit_onclause(self):
|
|
stmt = select(parent).join(child)
|
|
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT parent.id, parent.data FROM parent JOIN child "
|
|
"ON parent.id = child.parent_id",
|
|
)
|
|
|
|
def test_join_nofrom_implicit_left_side_implicit_onclause_3level(self):
|
|
stmt = select(parent).join(child).join(grandchild)
|
|
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT parent.id, parent.data FROM parent JOIN child "
|
|
"ON parent.id = child.parent_id "
|
|
"JOIN grandchild ON child.id = grandchild.child_id",
|
|
)
|
|
|
|
def test_join_nofrom_explicit_left_side_implicit_onclause(self):
|
|
stmt = select(parent).join_from(parent, child)
|
|
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT parent.id, parent.data FROM parent JOIN child "
|
|
"ON parent.id = child.parent_id",
|
|
)
|
|
|
|
def test_join_froms_implicit_left_side_explicit_onclause(self):
|
|
stmt = (
|
|
select(table1)
|
|
.select_from(table1)
|
|
.join(table2, table1.c.myid == table2.c.otherid)
|
|
)
|
|
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT mytable.myid, mytable.name, mytable.description "
|
|
"FROM mytable JOIN myothertable "
|
|
"ON mytable.myid = myothertable.otherid",
|
|
)
|
|
|
|
def test_join_froms_explicit_left_side_explicit_onclause(self):
|
|
stmt = (
|
|
select(table1)
|
|
.select_from(table1)
|
|
.join_from(table1, table2, table1.c.myid == table2.c.otherid)
|
|
)
|
|
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT mytable.myid, mytable.name, mytable.description "
|
|
"FROM mytable JOIN myothertable "
|
|
"ON mytable.myid = myothertable.otherid",
|
|
)
|
|
|
|
def test_join_froms_implicit_left_side_implicit_onclause(self):
|
|
stmt = select(parent).select_from(parent).join(child)
|
|
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT parent.id, parent.data FROM parent JOIN child "
|
|
"ON parent.id = child.parent_id",
|
|
)
|
|
|
|
def test_join_froms_explicit_left_side_implicit_onclause(self):
|
|
stmt = select(parent).select_from(parent).join_from(parent, child)
|
|
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT parent.id, parent.data FROM parent JOIN child "
|
|
"ON parent.id = child.parent_id",
|
|
)
|
|
|
|
def test_join_implicit_left_side_wo_cols_onelevel(self):
|
|
"""test issue #6503"""
|
|
stmt = select(parent).join(child).with_only_columns(child.c.id)
|
|
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT child.id FROM parent "
|
|
"JOIN child ON parent.id = child.parent_id",
|
|
)
|
|
|
|
def test_join_implicit_left_side_wo_cols_onelevel_union(self):
|
|
"""test issue #6698, regression from #6503.
|
|
|
|
this issue didn't affect Core but testing it here anyway."""
|
|
stmt = select(parent).join(child).with_only_columns(child.c.id)
|
|
|
|
stmt = stmt.union(select(child.c.id))
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT child.id FROM parent "
|
|
"JOIN child ON parent.id = child.parent_id "
|
|
"UNION "
|
|
"SELECT child.id FROM child",
|
|
)
|
|
|
|
def test_join_implicit_left_side_wo_cols_twolevel(self):
|
|
"""test issue #6503"""
|
|
stmt = (
|
|
select(parent)
|
|
.join(child)
|
|
.with_only_columns(child.c.id)
|
|
.join(grandchild)
|
|
.with_only_columns(grandchild.c.id)
|
|
)
|
|
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT grandchild.id FROM parent "
|
|
"JOIN child ON parent.id = child.parent_id "
|
|
"JOIN grandchild ON child.id = grandchild.child_id",
|
|
)
|
|
|
|
def test_join_implicit_left_side_wo_cols_twolevel_union(self):
|
|
"""test issue #6698, regression from #6503.
|
|
|
|
this issue didn't affect Core but testing it here anyway."""
|
|
stmt = (
|
|
select(parent)
|
|
.join(child)
|
|
.with_only_columns(child.c.id)
|
|
.join(grandchild)
|
|
.with_only_columns(grandchild.c.id)
|
|
)
|
|
|
|
stmt = union(stmt, select(grandchild.c.id))
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT grandchild.id FROM parent "
|
|
"JOIN child ON parent.id = child.parent_id "
|
|
"JOIN grandchild ON child.id = grandchild.child_id "
|
|
"UNION "
|
|
"SELECT grandchild.id FROM grandchild",
|
|
)
|
|
|
|
def test_right_nested_inner_join(self):
|
|
inner = child.join(grandchild)
|
|
|
|
stmt = select(parent).outerjoin_from(parent, inner)
|
|
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT parent.id, parent.data FROM parent "
|
|
"LEFT OUTER JOIN "
|
|
"(child JOIN grandchild ON child.id = grandchild.child_id) "
|
|
"ON parent.id = child.parent_id",
|
|
)
|
|
|
|
def test_joins_w_filter_by(self):
|
|
# Note: Both parent and child have a "data" column
|
|
# After the join, filter_by will see both entities
|
|
# To avoid ambiguity, filter first on parent before join, or use
|
|
# filter() with explicit column references
|
|
stmt = (
|
|
select(parent)
|
|
.filter_by(data="p1") # Filter parent.data before the join
|
|
.join(child)
|
|
.filter(child.c.data == "c1") # Explicit to avoid ambiguity
|
|
.join_from(table1, table2, table1.c.myid == table2.c.otherid)
|
|
.filter_by(otherid=5) # otherid is unambiguous
|
|
)
|
|
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT parent.id, parent.data FROM parent JOIN child "
|
|
"ON parent.id = child.parent_id, mytable JOIN myothertable "
|
|
"ON mytable.myid = myothertable.otherid "
|
|
"WHERE parent.data = :data_1 AND child.data = :data_2 "
|
|
"AND myothertable.otherid = :otherid_1",
|
|
checkparams={"data_1": "p1", "data_2": "c1", "otherid_1": 5},
|
|
)
|
|
|
|
def test_filter_by_from_col(self):
|
|
stmt = select(table1.c.myid).filter_by(name="foo")
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT mytable.myid FROM mytable WHERE mytable.name = :name_1",
|
|
)
|
|
|
|
def test_filter_by_from_func(self):
|
|
"""test #6414"""
|
|
stmt = select(func.count(table1.c.myid)).filter_by(name="foo")
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT count(mytable.myid) AS count_1 "
|
|
"FROM mytable WHERE mytable.name = :name_1",
|
|
)
|
|
|
|
def test_filter_by_from_func_not_the_first_arg(self):
|
|
"""test #6414"""
|
|
stmt = select(func.bar(True, table1.c.myid)).filter_by(name="foo")
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT bar(:bar_2, mytable.myid) AS bar_1 "
|
|
"FROM mytable WHERE mytable.name = :name_1",
|
|
)
|
|
|
|
def test_filter_by_from_cast(self):
|
|
"""test #6414"""
|
|
stmt = select(cast(table1.c.myid, Integer)).filter_by(name="foo")
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT CAST(mytable.myid AS INTEGER) AS myid "
|
|
"FROM mytable WHERE mytable.name = :name_1",
|
|
)
|
|
|
|
def test_filter_by_from_binary(self):
|
|
"""test #6414"""
|
|
stmt = select(table1.c.myid == 5).filter_by(name="foo")
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT mytable.myid = :myid_1 AS anon_1 "
|
|
"FROM mytable WHERE mytable.name = :name_1",
|
|
)
|
|
|
|
def test_filter_by_from_label(self):
|
|
"""test #6414"""
|
|
stmt = select(table1.c.myid.label("some_id")).filter_by(name="foo")
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT mytable.myid AS some_id "
|
|
"FROM mytable WHERE mytable.name = :name_1",
|
|
)
|
|
|
|
def test_filter_by_no_property_from_table(self):
|
|
assert_raises_message(
|
|
exc.InvalidRequestError,
|
|
'None of the FROM clause entities have a property "foo". '
|
|
"Searched entities: mytable",
|
|
select(table1).filter_by,
|
|
foo="bar",
|
|
)
|
|
|
|
def test_filter_by_no_property_from_col(self):
|
|
assert_raises_message(
|
|
exc.InvalidRequestError,
|
|
'None of the FROM clause entities have a property "foo". '
|
|
"Searched entities: mytable",
|
|
select(table1.c.myid).filter_by,
|
|
foo="bar",
|
|
)
|
|
|
|
def test_filter_by_across_join_entities_issue_8601(self):
|
|
"""Test issue #8601 - filter_by after with_only_columns."""
|
|
# The original failing case from issue #8601
|
|
# Use 'parent_id' which only exists in child table
|
|
stmt = (
|
|
select(parent)
|
|
.join(child)
|
|
.with_only_columns(parent.c.id)
|
|
.filter_by(parent_id=5)
|
|
)
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT parent.id FROM parent "
|
|
"JOIN child ON parent.id = child.parent_id "
|
|
"WHERE child.parent_id = :parent_id_1",
|
|
checkparams={"parent_id_1": 5},
|
|
)
|
|
|
|
def test_filter_by_ambiguous_column_error(self):
|
|
"""Test filter_by() raises AmbiguousColumnError."""
|
|
# Both parent and child have 'data' column
|
|
stmt = select(parent).join(child)
|
|
|
|
with expect_raises_message(
|
|
exc.AmbiguousColumnError,
|
|
'Attribute name "data" is ambiguous; it exists in multiple '
|
|
r"FROM clause entities \((?:parent(?:, )?"
|
|
r"|child(?:, )?){2}\).",
|
|
):
|
|
stmt.filter_by(data="foo")
|
|
|
|
def test_filter_by_unambiguous_across_joins(self):
|
|
"""Test filter_by finds unambiguous columns across multiple joins"""
|
|
# 'parent_id' only exists in child
|
|
stmt = select(parent).join(child).filter_by(parent_id=5)
|
|
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT parent.id, parent.data FROM parent "
|
|
"JOIN child ON parent.id = child.parent_id "
|
|
"WHERE child.parent_id = :parent_id_1",
|
|
checkparams={"parent_id_1": 5},
|
|
)
|
|
|
|
def test_filter_by_column_not_in_any_entity(self):
|
|
"""Test error when attribute not found in any FROM entity"""
|
|
stmt = select(parent).join(child)
|
|
|
|
with expect_raises_message(
|
|
exc.InvalidRequestError,
|
|
'None of the FROM clause entities have a property "nonexistent". '
|
|
r"Searched entities: (?:parent(?:, )?"
|
|
r"|child(?:, )?){2}",
|
|
):
|
|
stmt.filter_by(nonexistent="foo")
|
|
|
|
def test_filter_by_multiple_joins(self):
|
|
"""Test filter_by() with multiple joins"""
|
|
# grandchild has unique 'child_id' column
|
|
stmt = (
|
|
select(parent)
|
|
.join(child, parent.c.id == child.c.parent_id)
|
|
.join(grandchild, child.c.id == grandchild.c.child_id)
|
|
.filter_by(child_id=3)
|
|
)
|
|
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT parent.id, parent.data FROM parent "
|
|
"JOIN child ON parent.id = child.parent_id "
|
|
"JOIN grandchild ON child.id = grandchild.child_id "
|
|
"WHERE grandchild.child_id = :child_id_1",
|
|
checkparams={"child_id_1": 3},
|
|
)
|
|
|
|
def test_filter_by_explicit_from_with_join(self):
|
|
"""Test filter_by with explicit FROM and joins"""
|
|
stmt = select(parent.c.id).select_from(parent).join(child)
|
|
|
|
# Should be ambiguous since both have 'data'
|
|
with expect_raises_message(
|
|
exc.AmbiguousColumnError,
|
|
'Attribute name "data" is ambiguous; it exists in multiple '
|
|
r"FROM clause entities \((?:parent(?:, )?"
|
|
r"|child(?:, )?){2}\).",
|
|
):
|
|
stmt.filter_by(data="child_data")
|
|
|
|
def test_select_tuple_outer(self):
|
|
stmt = select(tuple_(table1.c.myid, table1.c.name))
|
|
|
|
assert_raises_message(
|
|
exc.CompileError,
|
|
r"Most backends don't support SELECTing from a tuple\(\) object. "
|
|
"If this is an ORM query, consider using the Bundle object.",
|
|
stmt.compile,
|
|
)
|
|
|
|
def test_select_tuple_subquery(self):
|
|
subq = select(
|
|
table1.c.name, tuple_(table1.c.myid, table1.c.name)
|
|
).subquery()
|
|
|
|
stmt = select(subq.c.name)
|
|
|
|
# if we aren't fetching it, then render it
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT anon_1.name FROM (SELECT mytable.name AS name, "
|
|
"(mytable.myid, mytable.name) AS anon_2 FROM mytable) AS anon_1",
|
|
)
|
|
|
|
@testing.combinations(
|
|
("union_all", "UNION ALL"),
|
|
("union", "UNION"),
|
|
("intersect_all", "INTERSECT ALL"),
|
|
("intersect", "INTERSECT"),
|
|
("except_all", "EXCEPT ALL"),
|
|
("except_", "EXCEPT"),
|
|
)
|
|
def test_select_multiple_compound_elements(self, methname, joiner):
|
|
stmt = select(literal(1))
|
|
meth = getattr(stmt, methname)
|
|
stmt = meth(select(literal(2)), select(literal(3)))
|
|
|
|
self.assert_compile(
|
|
stmt,
|
|
"SELECT :param_1 AS anon_1"
|
|
" %(joiner)s SELECT :param_2 AS anon_2"
|
|
" %(joiner)s SELECT :param_3 AS anon_3" % {"joiner": joiner},
|
|
)
|
|
|
|
@testing.combinations(
|
|
lambda stmt: stmt.with_statement_hint("some hint"),
|
|
lambda stmt: stmt.with_hint(table("x"), "some hint"),
|
|
lambda stmt: stmt.where(column("q") == 5),
|
|
lambda stmt: stmt.having(column("q") == 5),
|
|
lambda stmt: stmt.order_by(column("q")),
|
|
lambda stmt: stmt.group_by(column("q")),
|
|
# TODO: continue
|
|
)
|
|
def test_methods_generative(self, testcase):
|
|
s1 = select(1)
|
|
s2 = testing.resolve_lambda(testcase, stmt=s1)
|
|
|
|
assert s1 is not s2
|
|
|
|
|
|
class ColumnCollectionAsSelectTest(fixtures.TestBase, AssertsCompiledSQL):
|
|
"""tests related to #8285."""
|
|
|
|
__dialect__ = "default"
|
|
|
|
def test_c_collection_as_from(self):
|
|
stmt = select(parent.c)
|
|
|
|
# this works because _all_selected_columns expands out
|
|
# ClauseList. it does so in the same way that it works for
|
|
# Table already. so this is free
|
|
eq_(stmt._all_selected_columns, [parent.c.id, parent.c.data])
|
|
|
|
self.assert_compile(stmt, "SELECT parent.id, parent.data FROM parent")
|
|
|
|
def test_c_sub_collection_str_stmt(self):
|
|
stmt = select(table1.c["myid", "description"])
|
|
|
|
self.assert_compile(
|
|
stmt, "SELECT mytable.myid, mytable.description FROM mytable"
|
|
)
|
|
|
|
subq = stmt.subquery()
|
|
self.assert_compile(
|
|
select(subq.c[0]).where(subq.c.description == "x"),
|
|
"SELECT anon_1.myid FROM (SELECT mytable.myid AS myid, "
|
|
"mytable.description AS description FROM mytable) AS anon_1 "
|
|
"WHERE anon_1.description = :description_1",
|
|
)
|
|
|
|
def test_c_sub_collection_int_stmt(self):
|
|
stmt = select(table1.c[2, 0])
|
|
|
|
self.assert_compile(
|
|
stmt, "SELECT mytable.description, mytable.myid FROM mytable"
|
|
)
|
|
|
|
subq = stmt.subquery()
|
|
self.assert_compile(
|
|
select(subq.c.myid).where(subq.c[1] == "x"),
|
|
"SELECT anon_1.myid FROM (SELECT mytable.description AS "
|
|
"description, mytable.myid AS myid FROM mytable) AS anon_1 "
|
|
"WHERE anon_1.myid = :myid_1",
|
|
)
|
|
|
|
def test_c_sub_collection_str(self):
|
|
coll = table1.c["myid", "description"]
|
|
is_(coll.myid, table1.c.myid)
|
|
|
|
eq_(list(coll), [table1.c.myid, table1.c.description])
|
|
|
|
def test_c_sub_collection_int(self):
|
|
coll = table1.c[2, 0]
|
|
|
|
is_(coll.myid, table1.c.myid)
|
|
|
|
eq_(list(coll), [table1.c.description, table1.c.myid])
|
|
|
|
def test_c_sub_collection_positive_slice(self):
|
|
coll = table1.c[0:2]
|
|
|
|
is_(coll.myid, table1.c.myid)
|
|
is_(coll.name, table1.c.name)
|
|
|
|
eq_(list(coll), [table1.c.myid, table1.c.name])
|
|
|
|
def test_c_sub_collection_negative_slice(self):
|
|
coll = table1.c[-2:]
|
|
|
|
is_(coll.name, table1.c.name)
|
|
is_(coll.description, table1.c.description)
|
|
|
|
eq_(list(coll), [table1.c.name, table1.c.description])
|
|
|
|
def test_missing_key(self):
|
|
with expect_raises_message(KeyError, "unknown"):
|
|
table1.c["myid", "unknown"]
|
|
|
|
def test_missing_index(self):
|
|
with expect_raises_message(IndexError, "5"):
|
|
table1.c["myid", 5]
|