Index on hybrid expression fails with "sqlalchemy.exc.ArgumentError: Can't add unnamed column to column collection"

[Jack Zhou]

Considering the following model:

class Foo(Base):
    __tablename__ = "foo"
    id = Column(Integer, primary_key=True)
    foo = Column(String)

    @hybrid_property
    def expr(self):
        return self.id in (1,)

    @expr.expression
    def expr(cls):
        return cls.id.in_([1])

If I add an index like this:

Index("ix_foo_id", Foo.expr, Foo.foo)

it fails with

Traceback (most recent call last):
  File "sqlite.py", line 30, in <module>
    Index("ix_foo_id", Foo.expr, Foo.foo)
  File "/home/jack/dev/sqlalchemy/lib/sqlalchemy/sql/schema.py", line 3301, in __init__
    ColumnCollectionMixin.__init__(self, *columns)
  File "/home/jack/dev/sqlalchemy/lib/sqlalchemy/sql/schema.py", line 2501, in __init__
    self._check_attach()
  File "/home/jack/dev/sqlalchemy/lib/sqlalchemy/sql/schema.py", line 2557, in _check_attach
    self._set_parent_with_dispatch(tables.pop())
  File "/home/jack/dev/sqlalchemy/lib/sqlalchemy/sql/base.py", line 433, in _set_parent_with_dispatch
    self._set_parent(parent)
  File "/home/jack/dev/sqlalchemy/lib/sqlalchemy/sql/schema.py", line 3304, in _set_parent
    ColumnCollectionMixin._set_parent(self, table)
  File "/home/jack/dev/sqlalchemy/lib/sqlalchemy/sql/schema.py", line 2572, in _set_parent
    self.columns.add(col)
  File "/home/jack/dev/sqlalchemy/lib/sqlalchemy/sql/base.py", line 505, in add
    "Can't add unnamed column to column collection")
sqlalchemy.exc.ArgumentError: Can't add unnamed column to column collection

But it works if I just put in the expression directly:

Index("ix_foo_id", Foo.id.in_([1]), Foo.foo)

It also fails silently (no index is created) if the hybrid expression is the only expression in the list:

Index("ix_foo_id", Foo.expr)

It works in 1.0.14 correctly.

[Mike Bayer]

# 1.0:
# 1. index attempts to locate expressions
# 2. finds foo.c.id, Foo.foo
# 3. sends to Columnmixin
# 4. ColumnMixin does __clause_element__() to get foo.c.foo, already has foo.c.id
# 5. succeeds

# 1.1:
# 1. index attempts to locate expresions
# 2. finds Foo.expr, Foo.foo
# 3. sends to ColumnMixin
# 4. ColumnMixin does __clause_element__() to get Foo.id.in_([1]), foo.c.foo
# 5. fails on expr #1
ix = Index("ix_foo_id", Foo.expr, Foo.foo)

[Mike Bayer]

Index should extract clause_element() early

Fixed bug where :class:.Index would fail to extract columns from compound SQL expressions if those SQL expressions were wrapped inside of an ORM-style __clause_element__() construct. This bug exists in 1.0.x as well, however in 1.1 is more noticeable as hybrid_property @expression now returns a wrapped element.

Fixes: #3763

Change-Id: I992536386503a1fb3f2305790abe008d72c44c4a

→ <<cset 6327c59d4f34>>

[Jack Zhou]

The exception is fixed, but the index created appears to be incorrect.

Index("ix_foo_id", Foo.expr, Foo.foo)

produces

CREATE INDEX ix_foo_id ON foo (id, foo)

while

Index("ix_foo_id", Foo.id.in_([1]), Foo.foo)

produces

CREATE INDEX ix_foo_id ON foo (id IN (1), foo)

[Mike Bayer]

Ensure post-clause_element() expression are used in Index

The change in Index for 1.1 combined with the fix for ref #3763 still fails to deliver the correct object resolved by clause_element() to the list of expressions for compilation. Make sure we use the expression that's been unwrapped from clause_element().

Change-Id: Ie1df8db5090de665048331786f0024d52851923f Fixes: #3763

→ <<cset 1269b08b1bae>>

[Mike Bayer]

Ensure post-clause_element() expression are used in Index

The change in Index for 1.1 combined with the fix for ref #3763 still fails to deliver the correct object resolved by clause_element() to the list of expressions for compilation. Make sure we use the expression that's been unwrapped from clause_element().

Change-Id: Ie1df8db5090de665048331786f0024d52851923f Fixes: #3763

→ <<cset 1269b08b1bae>>