sqlalchemy-tickets — trac tickets and updates forwarded to this list

[Sqlalchemy-tickets] [sqlalchemy] #2745: PostgreSQL ARRAY of Enum doesn't create Enum types before creating table

[sqlalchemy <mi...@zz...>]

#2745: PostgreSQL ARRAY of Enum doesn't create Enum types before creating table
-----------------------+-----------------------------------------
 Reporter:  glyphobet  |           Owner:  zzzeek
     Type:  defect     |          Status:  new
 Priority:  medium     |       Milestone:
Component:  postgres   |        Severity:  no triage selected yet
 Keywords:             |  Progress State:  awaiting triage
-----------------------+-----------------------------------------
 In PostgreSQL, when defining a column of type ARRAY of Enum, SQLAlchemy
 doesn't create the Enum types before creating the table. This causes the
 table creation to fail.

 This bug occurs with SQLAlchemy 0.8.1.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2745>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2734: __eq__ operator appears to not be working correctly for decorated types

[sqlalchemy <mi...@zz...>]

#2734: __eq__ operator appears to not be working correctly for decorated types
-----------------------------------+------------------------------------
      Reporter:  mcclurem          |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  medium            |  Milestone:
     Component:  declarative       |   Severity:  no triage selected yet
    Resolution:  worksforme        |   Keywords:  TypeDecorator
Progress State:  completed/closed  |
-----------------------------------+------------------------------------

Comment (by zzzeek):

 the patch in #2744 allows this workaround to be performed more simply:

 {{{
 #!python
 class NullableString(TypeDecorator):
     '''Turns None into the string "Null" and back in order to prevent
 Null!=Null issues'''

     impl = String

     coerce_to_is_types = ()

     def process_bind_param(self, value, dialect):
         return "NONE" if value is None else value

     def process_result_value(self, value, dialect):
         return None if value == "NONE" else value
 }}}

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2734#comment:4>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2744: Consider special flags/logic/other in TypeDecorator to ease production of custom `__eq__()` logic

[sqlalchemy <mi...@zz...>]

#2744: Consider special flags/logic/other in TypeDecorator to ease production of
custom `__eq__()` logic
------------------------------+-------------------------------
      Reporter:  vladimir-lu  |      Owner:  zzzeek
          Type:  defect       |     Status:  new
      Priority:  high         |  Milestone:  0.8.xx
     Component:  sql          |   Severity:  major - 1-3 hours
    Resolution:               |   Keywords:
Progress State:  needs tests  |
------------------------------+-------------------------------
Changes (by zzzeek):

 * status_field:  not decided upon => needs tests


Comment:

 working patch is attached.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2744#comment:2>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2734: __eq__ operator appears to not be working correctly for decorated types

[sqlalchemy <mi...@zz...>]

#2734: __eq__ operator appears to not be working correctly for decorated types
-----------------------------------+------------------------------------
      Reporter:  mcclurem          |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  medium            |  Milestone:
     Component:  declarative       |   Severity:  no triage selected yet
    Resolution:  worksforme        |   Keywords:  TypeDecorator
Progress State:  completed/closed  |
-----------------------------------+------------------------------------

Comment (by zzzeek):

 see also #2744, where it seems apparent some kind of helper should be
 added to `TypeDecorator` to help this situation (or at least
 documentation).

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2734#comment:3>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2744: Consider special flags/logic/other in TypeDecorator to ease production of custom `__eq__()` logic (was: Custom TypeDecorator can no longer convert boolean type to another type)

[sqlalchemy <mi...@zz...>]

#2744: Consider special flags/logic/other in TypeDecorator to ease production of
custom `__eq__()` logic
-----------------------------------+-------------------------------
      Reporter:  vladimir-lu       |      Owner:  zzzeek
          Type:  defect            |     Status:  new
      Priority:  high              |  Milestone:  0.8.xx
     Component:  sql               |   Severity:  major - 1-3 hours
    Resolution:                    |   Keywords:
Progress State:  not decided upon  |
-----------------------------------+-------------------------------
Changes (by zzzeek):

 * priority:  medium => high
 * status_field:  awaiting triage => not decided upon
 * severity:  minor - half an hour => major - 1-3 hours


Comment:

 please use the workaround in #2734, applied to True/False.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2744#comment:1>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

[Sqlalchemy-tickets] [sqlalchemy] #2744: Custom TypeDecorator can no longer convert boolean type to another type

[sqlalchemy <mi...@zz...>]

#2744: Custom TypeDecorator can no longer convert boolean type to another type
-------------------------+---------------------------------------
 Reporter:  vladimir-lu  |           Owner:  zzzeek
     Type:  defect       |          Status:  new
 Priority:  medium       |       Milestone:  0.8.xx
Component:  sql          |        Severity:  minor - half an hour
 Keywords:               |  Progress State:  awaiting triage
-------------------------+---------------------------------------
 This is a 0.8.0 -> 0.8.1 regression. Basically, I have a custom type
 decorator like the following:

 {{{
 class BitIndicatorType(types.TypeDecorator):
     """
     Converts '0' and '1' bits to True/False values and vica-versa
     """

     impl = types.SmallInteger

     def process_bind_param(self, value, dialect):
         return 1 if value else 0

     def process_result_value(self, value, dialect):
         return True if value == 1 else False

     def copy(self):
         return BitIndicatorType()
 }}}

 This type in turn is used in ORM mapped objects and in queries - so for
 example if we have enabledInd = Column('enabled_ind', BitIndicatorType)
 and obj.enabledInd == True then I expect the SQL to be: {{{... where
 enabled_ind = 1...}}}

 However, due to
 https://github.com/zzzeek/sqlalchemy/commit/5884c2e7e5b46cee29b90aa3f7161e7380e3e2a5
 the explicit boolean check added in expression.py _const_expr, the true
 gets passed to the dialect directly before being passed through the type
 decorator.

 The expected behaviour is that the type decorator be checked even when the
 value is a boolean value.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2744>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2743: Backref broken for detached objects

[sqlalchemy <mi...@zz...>]

#2743: Backref broken for detached objects
-----------------------------------+------------------------------------
      Reporter:  schlamar          |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  medium            |  Milestone:
     Component:  orm               |   Severity:  no triage selected yet
    Resolution:  worksforme        |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+------------------------------------
Changes (by zzzeek):

 * status:  new => closed
 * resolution:   => worksforme
 * status_field:  awaiting triage => completed/closed


Comment:

 This behavior is by design.   Implicitly populating the reverse side of a
 collection would add significant overhead to the object loading process,
 when it has not been requested (and you can request it, see below).
 Additionally, reverse-side collection population is not even feasible when
 the relationship is a many-to-many - if you had a many to many between
 A<->B, and you were to load a subset of A "ASub", which then loads the
 list of B objects "BASub", those B objects may refer to many more "A"
 objects outside of "ASub" and you'd see a lot more rows being loaded than
 were requested.

 In this case, the situation between eager loading the one-to-many also
 resolving the many-to-one can be achieved most efficiently using the
 `immediateload()` directive, since these many-to-ones can all be pulled
 from the identity map with no additional SQL:

 {{{
 #!python
     parents = session.query(Parent).options(joinedload('children'),
 immediateload("children.parent")).all()
     session.expunge_all()
     print parents
     print parents[0].children
     print parents[0].children[0].parent
 }}}

 Note that SQLAlchemy orients itself towards the "attached" object use
 case; detachment's primary use case is that of transferring or caching
 objects to be reattached to another `Session` for subsequent usage.  While
 working with objects in an explicitly detached state is supported to some
 degree, it will always have caveats.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2743#comment:2>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2743: Backref broken for detached objects

[sqlalchemy <mi...@zz...>]

#2743: Backref broken for detached objects
----------------------------------+------------------------------------
      Reporter:  schlamar         |      Owner:  zzzeek
          Type:  defect           |     Status:  new
      Priority:  medium           |  Milestone:
     Component:  orm              |   Severity:  no triage selected yet
    Resolution:                   |   Keywords:
Progress State:  awaiting triage  |
----------------------------------+------------------------------------

Comment (by schlamar):

 Workaround would be:


 {{{
 for p in parents:
     for c in p.children:
         getattr(c, '__dict__')['parent'] = p
 print parents[0].children[0].parent
 }}}

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2743#comment:1>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

[Sqlalchemy-tickets] [sqlalchemy] #2743: Backref broken for detached objects

[sqlalchemy <mi...@zz...>]

#2743: Backref broken for detached objects
----------------------+-----------------------------------------
 Reporter:  schlamar  |           Owner:  zzzeek
     Type:  defect    |          Status:  new
 Priority:  medium    |       Milestone:
Component:  orm       |        Severity:  no triage selected yet
 Keywords:            |  Progress State:  awaiting triage
----------------------+-----------------------------------------
 You cannot traverse a backref if the object is detached and the
 relationship is already joined.

 Testcase:


 {{{
 #!python
 Base = declarative_base()


 class Parent(Base):
     __tablename__ = 'parent'
     id = Column(Integer, primary_key=True)


 class Child(Base):
     __tablename__ = 'child'
     id = Column(Integer, primary_key=True)
     parent_id = Column(Integer, ForeignKey('parent.id'))
     parent = relationship('Parent', backref='children')


 def main():
     engine = create_engine('sqlite:///:memory:')
     Base.metadata.create_all(engine)
     Session = sessionmaker(bind=engine)
     session = Session()

     p = Parent()
     p.children.append(Child())
     session.add(p)
     session.commit()

     parents = session.query(Parent).options(joinedload('children')).all()
     session.expunge_all()
     print parents
     print parents[0].children
     print parents[0].children[0].parent
 }}}

 Fails with:

 {{{
 Traceback (most recent call last):
   File "testcase.py", line 44, in <module>
     main()
   File "testcase.py", line 35, in main
     print parents[0].children[0].parent
   File
 "/Users/marc/Documents/python/sqlalchemy/lib/sqlalchemy/orm/attributes.py",
 line 316, in __get__
     return self.impl.get(instance_state(instance), dict_)
   File
 "/Users/marc/Documents/python/sqlalchemy/lib/sqlalchemy/orm/attributes.py",
 line 613, in get
     value = self.callable_(state, passive)
   File
 "/Users/marc/Documents/python/sqlalchemy/lib/sqlalchemy/orm/strategies.py",
 line 497, in _load_for_state
     (orm_util.state_str(state), self.key)
 sqlalchemy.orm.exc.DetachedInstanceError: Parent instance <Child at
 0x10654ffd0> is not bound to a Session; lazy load operation of attribute
 'parent' cannot proceed

 }}}

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2743>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2742: look into using inline=true for bindparam() inside of DDL sequences

[sqlalchemy <mi...@zz...>]

#2742: look into using inline=true for bindparam() inside of DDL sequences
------------------------------+----------------------------------
      Reporter:  andreycizov  |      Owner:  zzzeek
          Type:  defect       |     Status:  new
      Priority:  medium       |  Milestone:  0.8.xx
     Component:  schema       |   Severity:  minor - half an hour
    Resolution:               |   Keywords:
Progress State:  in queue     |
------------------------------+----------------------------------

Comment (by andreycizov):

 This works on PG 9.2.3:
 {{{
 CREATE INDEX heuristic ON foos ((1 / (a + b)))
 }}}

 That doesn't as well:
 {{{
 CREATE INDEX heuristic ON foos (1 / (a + b))
 }}}

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2742#comment:3>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2742: look into using inline=true for bindparam() inside of DDL sequences (was: Base.metadata.create_all() fails to create expression indexes with literals)

[sqlalchemy <mi...@zz...>]

#2742: look into using inline=true for bindparam() inside of DDL sequences
------------------------------+----------------------------------
      Reporter:  andreycizov  |      Owner:  zzzeek
          Type:  defect       |     Status:  new
      Priority:  medium       |  Milestone:  0.8.xx
     Component:  schema       |   Severity:  minor - half an hour
    Resolution:               |   Keywords:
Progress State:  in queue     |
------------------------------+----------------------------------
Changes (by zzzeek):

 * milestone:   => 0.8.xx
 * status_field:  awaiting triage => in queue
 * component:  cextensions => schema
 * severity:  no triage selected yet => minor - half an hour


Comment:

 well you can't use a bound parameter in an Index.   The system here should
 tell the compiler to convert bound parameters to literals, so that can be
 fixed, but in the meantime just use text() or literal_column() (btw this
 index still returns an error for me on PG 9.1.4):

 {{{
     Index('heuristic',
           (text("1")) / (Foo.a + Foo.b))

 }}}


 error:

 {{{
 ProgrammingError: (ProgrammingError) syntax error at or near "1"
 LINE 1: CREATE INDEX heuristic ON foos (1 / (a + b))
 }}}

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2742#comment:2>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2742: Base.metadata.create_all() fails to create expression indexes with literals

[sqlalchemy <mi...@zz...>]

#2742: Base.metadata.create_all() fails to create expression indexes with literals
----------------------------------+------------------------------------
      Reporter:  andreycizov      |      Owner:  zzzeek
          Type:  defect           |     Status:  new
      Priority:  medium           |  Milestone:
     Component:  cextensions      |   Severity:  no triage selected yet
    Resolution:                   |   Keywords:
Progress State:  awaiting triage  |
----------------------------------+------------------------------------

Comment (by andreycizov):

 I have used the wrong Component for this ticket and I can't seem to be
 able to edit it, so please note that.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2742#comment:1>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

[Sqlalchemy-tickets] [sqlalchemy] #2742: Base.metadata.create_all() fails to create expression indexes with literals

[sqlalchemy <mi...@zz...>]

#2742: Base.metadata.create_all() fails to create expression indexes with literals
-------------------------+-----------------------------------------
 Reporter:  andreycizov  |           Owner:  zzzeek
     Type:  defect       |          Status:  new
 Priority:  medium       |       Milestone:
Component:  cextensions  |        Severity:  no triage selected yet
 Keywords:               |  Progress State:  awaiting triage
-------------------------+-----------------------------------------
 The bug is reproducible on '''0.8.1''' and the git master version
 (!__version!__ == '''0.9.0''')

 I am trying to add an index on an expression for PostgreSQL:
 {{{
 Index('heuristic', (1.) / (Foo.a + Foo.b))
 }}}

 The SQL generated by SQLA is correct: `2013-06-05 09:45:06,668 INFO
 sqlalchemy.engine.base.Engine CREATE INDEX heuristic ON foos (%(param_1)s
 / (a + b))`, but `param_1` is never passed to the query executor so the
 query fails.

 Traceback:
 {{{
 Traceback (most recent call last):
   File "\sqla_bug\bugreport.py", line 24, in <module>
     Base.metadata.create_all(engine)
   File "\sqla_bug\sqlalchemy\schema.py", line 2781,in create_all
     tables=tables)
   File "\sqla_bug\sqlalchemy\engine\base.py", line 1475, in _run_visitor
     conn._run_visitor(visitorcallable, element, **kwargs)
   File "\sqla_bug\sqlalchemy\engine\base.py", line 1118, in _run_visitor
     **kwargs).traverse_single(element)
   File "\sqla_bug\sqlalchemy\sql\visitors.py", line 108, in
 traverse_single
     return meth(obj, **kw)
   File "\sqla_bug\sqlalchemy\engine\ddl.py", line 70, in visit_metadata
     self.traverse_single(table, create_ok=True)
   File "\sqla_bug\sqlalchemy\sql\visitors.py", line 108, in
 traverse_single
     return meth(obj, **kw)
   File "\sqla_bug\sqlalchemy\engine\ddl.py", line 93, in visit_table
     self.traverse_single(index)
   File "\sqla_bug\sqlalchemy\sql\visitors.py", line 108, in
 traverse_single
     return meth(obj, **kw)
   File "\sqla_bug\sqlalchemy\engine\ddl.py", line 105, in visit_index
     self.connection.execute(schema.CreateIndex(index))
   File "\sqla_bug\sqlalchemy\engine\base.py", line662, in execute
     params)
   File "\sqla_bug\sqlalchemy\engine\base.py", line720, in _execute_ddl
     compiled
   File "\sqla_bug\sqlalchemy\engine\base.py", line876, in _execute_context
     context)
   File "\sqla_bug\sqlalchemy\engine\base.py", line1023, in
 _handle_dbapi_exception
     util.reraise(*exc_info)
   File "\sqla_bug\sqlalchemy\engine\base.py", line869, in _execute_context
     context)
   File "\sqla_bug\sqlalchemy\engine\default.py", line 326, in do_execute
     cursor.execute(statement, parameters)
 KeyError: 'param_1'
 }}}

 I've attached to code to reproduce the bug.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2742>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2369: many-to-many + single table joined eager fails since we don't parenthesize joins

[sqlalchemy <mi...@zz...>]

#2369: many-to-many + single table joined eager fails since we don't parenthesize
joins
---------------------------+---------------------------------------
      Reporter:  zzzeek    |      Owner:  zzzeek
          Type:  defect    |     Status:  new
      Priority:  high      |  Milestone:  0.9.0
     Component:  orm       |   Severity:  very major - up to 2 days
    Resolution:            |   Keywords:
Progress State:  in queue  |
---------------------------+---------------------------------------

Comment (by zzzeek):

 so with nested join support working, I thought we could do this:

 {{{
 #!diff
 diff --git a/lib/sqlalchemy/orm/util.py b/lib/sqlalchemy/orm/util.py
 index c21e7ea..ff71e39 100644
 --- a/lib/sqlalchemy/orm/util.py
 +++ b/lib/sqlalchemy/orm/util.py
 @@ -916,8 +916,18 @@ class _ORMJoin(expression.Join):
                              of_type=right_info.mapper)

              if sj is not None:
 -                left = sql.join(left, secondary, pj, isouter)
 -                onclause = sj
 +                if not isouter:
 +                    # old way: left [OUTER] JOIN secondary [OUTER] JOIN
 right.
 +                    # for an inner join, this is still OK.
 +                    left = sql.join(left, secondary, pj, isouter)
 +                    onclause = sj
 +                else:
 +                    # new way: left [OUTER] JOIN (secondary INNER JOIN
 right)
 +                    # this is [ticket:2369].  For an outer join, we can
 +                    # get more correctness by using a nested inner
 +                    # for secondary->right
 +                    right = sql.join(secondary, right, sj)
 +                    onclause = pj
              else:
                  onclause = pj
              self._target_adapter = target_adapter
 }}}

 however, this only works for one-level deep eager loading.   the test we
 have like test_froms->SelectFromTest.test_more_joins is doing a huge
 eagerload across two m2ms, and with this it gets the wrong result.  The
 old query there is:

 {{{
 SELECT anon_1.id AS anon_1_id, anon_1.name AS anon_1_name,
         keywords_1.id AS keywords_1_id, keywords_1.name AS
 keywords_1_name,
         items_1.id AS items_1_id, items_1.description AS
 items_1_description,
         orders_1.id AS orders_1_id, orders_1.user_id AS orders_1_user_id,
         orders_1.address_id AS orders_1_address_id,
         orders_1.description AS orders_1_description, orders_1.isopen AS
 orders_1_isopen

 FROM (
         SELECT users.id AS id, users.name AS name
         FROM users
         WHERE users.id IN (%(id_1)s, %(id_2)s)
 ) AS anon_1

         JOIN orders AS orders_2 ON anon_1.id = orders_2.user_id

         JOIN order_items AS order_items_1 ON orders_2.id =
 order_items_1.order_id

         JOIN items AS items_2 ON items_2.id = order_items_1.item_id

         JOIN item_keywords AS item_keywords_1 ON items_2.id =
 item_keywords_1.item_id

         JOIN keywords AS keywords_2 ON keywords_2.id =
 item_keywords_1.keyword_id

         LEFT OUTER JOIN orders AS orders_1 ON anon_1.id = orders_1.user_id

         LEFT OUTER JOIN order_items AS order_items_2 ON orders_1.id =
 order_items_2.order_id

         LEFT OUTER JOIN items AS items_1 ON items_1.id =
 order_items_2.item_id

         LEFT OUTER JOIN item_keywords AS item_keywords_2 ON items_1.id =
 item_keywords_2.item_id

         LEFT OUTER JOIN keywords AS keywords_1 ON keywords_1.id =
 item_keywords_2.keyword_id

 WHERE keywords_2.name IN (%(name_1)s, %(name_2)s, %(name_3)s) ORDER BY
 anon_1.id, orders_1.id, items_1.id, keywords_1.id
 }}}

 the new one is:

 {{{
 SELECT anon_1.id AS anon_1_id, anon_1.name AS anon_1_name,
         keywords_1.id AS keywords_1_id, keywords_1.name AS
 keywords_1_name,
         items_1.id AS items_1_id, items_1.description AS
 items_1_description,
         orders_1.id AS orders_1_id, orders_1.user_id AS orders_1_user_id,
         orders_1.address_id AS orders_1_address_id,
         orders_1.description AS orders_1_description, orders_1.isopen AS
 orders_1_isopen

 FROM (
         SELECT users.id AS id, users.name AS name
         FROM users
         WHERE users.id IN (%(id_1)s, %(id_2)s)
 ) AS anon_1

         JOIN orders AS orders_2 ON anon_1.id = orders_2.user_id

         JOIN order_items AS order_items_1 ON orders_2.id =
 order_items_1.order_id

         JOIN items AS items_2 ON items_2.id = order_items_1.item_id

         JOIN item_keywords AS item_keywords_1 ON items_2.id =
 item_keywords_1.item_id

         JOIN keywords AS keywords_2 ON keywords_2.id =
 item_keywords_1.keyword_id

         LEFT OUTER JOIN orders AS orders_1 ON anon_1.id = orders_1.user_id

         LEFT OUTER JOIN (
                 order_items AS order_items_2 JOIN items AS items_1 ON
 items_1.id = order_items_2.item_id
         ) ON orders_1.id = order_items_2.order_id

         LEFT OUTER JOIN (
                 item_keywords AS item_keywords_2 JOIN keywords AS
 keywords_1 ON keywords_1.id = item_keywords_2.keyword_id
         ) ON items_2.id = item_keywords_2.item_id

 WHERE keywords_2.name IN (%(name_1)s, %(name_2)s, %(name_3)s) ORDER BY
 items_1.id, keywords_1.id
 }}}

 so note, the problem here is that the subsequent eager joins aren't being
 nested.  So we do have to make sure if we do this, we have to change the
 whole way the joins write out for an eager load so that they subsequently
 nest, otherwise we're definitely not getting the right answer.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2369#comment:7>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2741: uselist=False collection not backref deferred, leads to unwanted load/autoflush

[sqlalchemy <mi...@zz...>]

#2741: uselist=False collection not backref deferred, leads to unwanted
load/autoflush
-----------------------------------+-------------------------------
      Reporter:  zzzeek            |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  highest           |  Milestone:  0.8.xx
     Component:  orm               |   Severity:  major - 1-3 hours
    Resolution:  worksforme        |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+-------------------------------

Comment (by zzzeek):

 we can also get the "multiple rows" condition by just turning off
 active_history:

 {{{
 #!python
 A.b.impl.active_history = False

 b1.a = a2

 sess.commit()

 print a2.b
 }}}

 it might be an interesting feature to allow users to override
 active_history, right now you can turn it on if not already, but not turn
 it off at the configuration level.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2741#comment:3>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2741: uselist=False collection not backref deferred, leads to unwanted load/autoflush

[sqlalchemy <mi...@zz...>]

#2741: uselist=False collection not backref deferred, leads to unwanted
load/autoflush
-----------------------------------+-------------------------------
      Reporter:  zzzeek            |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  highest           |  Milestone:  0.8.xx
     Component:  orm               |   Severity:  major - 1-3 hours
    Resolution:  worksforme        |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+-------------------------------
Changes (by zzzeek):

 * status:  new => closed
 * resolution:   => worksforme
 * status_field:  in queue => completed/closed


--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2741#comment:2>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2741: uselist=False collection not backref deferred, leads to unwanted load/autoflush

[sqlalchemy <mi...@zz...>]

#2741: uselist=False collection not backref deferred, leads to unwanted
load/autoflush
---------------------------+-------------------------------
      Reporter:  zzzeek    |      Owner:  zzzeek
          Type:  defect    |     Status:  new
      Priority:  highest   |  Milestone:  0.8.xx
     Component:  orm       |   Severity:  major - 1-3 hours
    Resolution:            |   Keywords:
Progress State:  in queue  |
---------------------------+-------------------------------

Comment (by zzzeek):

 the difference between uselist=True and uselist=False is that the old
 value must be *removed*.  Plenty of tests in test_backref_mutations test
 this kind of thing.  Suppose we disable the load on backref:

 {{{
 #!diff
 diff --git a/lib/sqlalchemy/orm/attributes.py
 b/lib/sqlalchemy/orm/attributes.py
 index bfba695..4ff6261 100644
 --- a/lib/sqlalchemy/orm/attributes.py
 +++ b/lib/sqlalchemy/orm/attributes.py
 @@ -784,7 +784,7 @@ class ScalarObjectAttributeImpl(ScalarAttributeImpl):
          if initiator and initiator.parent_token is self.parent_token:
              return

 -        if self.dispatch._active_history:
 +        if passive is PASSIVE_OFF and self.dispatch._active_history:
              old = self.get(state, dict_, passive=PASSIVE_ONLY_PERSISTENT)
          else:
              old = self.get(state, dict_, passive=PASSIVE_NO_FETCH)
 }}}

 then run the test like this:

 {{{
 #!python
 from sqlalchemy import *
 from sqlalchemy.orm import *
 from sqlalchemy.ext.declarative import declarative_base

 Base = declarative_base()

 class A(Base):
     __tablename__ = 'a'

     id = Column(Integer, primary_key=True)
     b = relationship("B", uselist=False, backref="a")

 class B(Base):
     __tablename__ = 'b'

     id = Column(Integer, primary_key=True)
     a_id = Column(Integer, ForeignKey('a.id'), nullable=False)

 e = create_engine("sqlite://", echo='debug')
 Base.metadata.create_all(e)

 sess = Session(e)

 a1 = A()
 b1 = B(a=a1)
 a2 = A()
 b2 = B(a=a2)

 sess.add_all([a1, b1, a2, b2])
 sess.commit()

 b1.a = a2

 sess.commit()

 print a2.b
 }}}

 `a2.b` now has *two* b's attached and `b2` has not been replaced by `b1`,
 we get:

 {{{
 SELECT b.id AS b_id, b.a_id AS b_a_id
 FROM b
 WHERE ? = b.a_id
 2013-06-04 12:28:23,543 INFO sqlalchemy.engine.base.Engine (2,)
 2013-06-04 12:28:23,543 DEBUG sqlalchemy.engine.base.Engine Col ('b_id',
 'b_a_id')
 2013-06-04 12:28:23,543 DEBUG sqlalchemy.engine.base.Engine Row (1, 2)
 2013-06-04 12:28:23,543 DEBUG sqlalchemy.engine.base.Engine Row (2, 2)
 /Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/strategies.py:524:
 SAWarning: Multiple rows returned with uselist=False for lazily-loaded
 attribute 'A.b'
   return self._emit_lazyload(session, state, ident_key, passive)
 <__main__.B object at 0x10161f7d0>
 }}}

 in this case, if we stick to the plan and do no_autoflush:

 {{{
 with sess.no_autoflush:
     b1.a = a2

 sess.commit()

 print a2.b
 }}}

 we still get an integrity error, because the replacement of `b2` throws a
 not null exception, but this is what we want in this case, since
 uselist=False means the old value must be replaced.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2741#comment:1>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

[Sqlalchemy-tickets] [sqlalchemy] #2741: uselist=False collection not backref deferred, leads to unwanted load/autoflush

[sqlalchemy <mi...@zz...>]

#2741: uselist=False collection not backref deferred, leads to unwanted
load/autoflush
---------------------+------------------------------------
 Reporter:  zzzeek   |           Owner:  zzzeek
     Type:  defect   |          Status:  new
 Priority:  highest  |       Milestone:  0.8.xx
Component:  orm      |        Severity:  major - 1-3 hours
 Keywords:           |  Progress State:  in queue
---------------------+------------------------------------
 {{{
 #!python
 from sqlalchemy import *
 from sqlalchemy.orm import *
 from sqlalchemy.ext.declarative import declarative_base

 Base = declarative_base()

 class A(Base):
     __tablename__ = 'a'

     id = Column(Integer, primary_key=True)
     b = relationship("B", uselist=False, backref="a")

 class B(Base):
     __tablename__ = 'b'

     id = Column(Integer, primary_key=True)
     a_id = Column(Integer, ForeignKey('a.id'), nullable=False)

 e = create_engine("sqlite://", echo=True)
 Base.metadata.create_all(e)

 sess = Session(e)

 a1 = A()
 b1 = B(a=a1)
 sess.add(a1)

 a2 = A()
 sess.add(a2)

 sess.commit()

 b1 = sess.query(B).first()

 # this is needed to trigger it
 b1.a


 b1.a = a2
 }}}

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2741>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

[Sqlalchemy-tickets] [sqlalchemy] #2740: remove the ::interval cast in the postgresql dialect

[sqlalchemy <mi...@zz...>]

#2740: remove the ::interval cast in the postgresql dialect
----------------------+---------------------------------------
 Reporter:  zzzeek    |           Owner:  zzzeek
     Type:  defect    |          Status:  new
 Priority:  medium    |       Milestone:  0.9.0
Component:  postgres  |        Severity:  minor - half an hour
 Keywords:            |  Progress State:  in queue
----------------------+---------------------------------------
 this seems to be there since it's beginning (see raca84bebb091a51ceeb),
 but gets in the way when timezone-aware comparisons are needed.   leave it
 up to the DBAPI to handle typecasts now, and if we really needed a type-
 specific workaround we could even use type-level SQL expressions.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2740>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2684: mutually dependent object cycle, detection w/ m2o/o2m without the backref

[sqlalchemy <mi...@zz...>]

#2684: mutually dependent object cycle, detection w/ m2o/o2m without the backref
-----------------------------------+-------------------------------
      Reporter:  vsevolod_fedorov  |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  medium            |  Milestone:  0.8.xx
     Component:  orm               |   Severity:  major - 1-3 hours
    Resolution:  wontfix           |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+-------------------------------
Changes (by zzzeek):

 * status:  new => closed
 * resolution:   => wontfix
 * status_field:  in queue => completed/closed


Comment:

 I think im going to close this because a mutually dependent FK situation
 will need post_update in the vast majority of cases anyway, and to try to
 get it to work around the case when there aren't actually any
 insert/delete cycles is quite difficult - it would be code that almost
 never gets used since post_update would normally be on anyway.

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2684#comment:3>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2721: SQLAlchemy leaks the deferrable keyword in FKs for mysql databases

[sqlalchemy <mi...@zz...>]

#2721: SQLAlchemy leaks the deferrable keyword in FKs for mysql databases
-----------------------------------+-----------------------------------
      Reporter:  kashif            |      Owner:
          Type:  defect            |     Status:  closed
      Priority:  medium            |  Milestone:  0.8.xx
     Component:  mysql             |   Severity:  trivial - <10 minutes
    Resolution:  fixed             |   Keywords:  mysql deferrable
Progress State:  completed/closed  |
-----------------------------------+-----------------------------------
Changes (by zzzeek):

 * status:  new => closed
 * resolution:   => fixed
 * severity:  minor - half an hour => trivial - <10 minutes
 * status_field:  in queue => completed/closed


Comment:

 rcc1ea5da10d2424ea2887a68fddfe07a3928c616 0.8

 rada19275299f0105f4aaed5bbe 0.9

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2721#comment:7>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2730: MutableDict does not detect clear() call

[sqlalchemy <mi...@zz...>]

#2730: MutableDict does not detect clear() call
-----------------------------------+-----------------------------------
      Reporter:  wichert           |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  medium            |  Milestone:  0.8.xx
     Component:  ext               |   Severity:  trivial - <10 minutes
    Resolution:  fixed             |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+-----------------------------------
Changes (by zzzeek):

 * status:  new => closed
 * resolution:   => fixed
 * severity:  minor - half an hour => trivial - <10 minutes
 * status_field:  in queue => completed/closed


Comment:

 rc2bbdf7770337c8b2cfb4e7340d06c895b171b0b 0.8

 r3a13047fb06d698e04408 0.9

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2730#comment:2>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2738: SQLAlchemy should throw an exception if there are multiple foreign keys to relate

[sqlalchemy <mi...@zz...>]

#2738: SQLAlchemy should throw an exception if there are multiple foreign keys to
relate
-----------------------------------+----------------------------------
      Reporter:  Svenstaro         |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  high              |  Milestone:  0.8.xx
     Component:  schema            |   Severity:  minor - half an hour
    Resolution:  fixed             |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+----------------------------------
Changes (by zzzeek):

 * status:  new => closed
 * resolution:   => fixed
 * status_field:  needs tests => completed/closed


Comment:

 r1fc2611e20ce94d3bbefc6910ba1127c400195b5 0.8

 r8a865a4d1f7bf7d399a551ae1c3f93 0.9

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2738#comment:3>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2728: potential glitch in schema calcs for metadata.reflect

[sqlalchemy <mi...@zz...>]

#2728: potential glitch in schema calcs for metadata.reflect
-----------------------------------+-------------------------------
      Reporter:  zzzeek            |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  high              |  Milestone:  0.8.xx
     Component:  schema            |   Severity:  major - 1-3 hours
    Resolution:  fixed             |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+-------------------------------
Changes (by zzzeek):

 * status:  new => closed
 * resolution:   => fixed
 * status_field:  needs tests => completed/closed


Comment:

 rec04620f1fe609881ed 0.9
 rf38e2aa46064f4a296 0.8

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2728#comment:3>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python

Re: [Sqlalchemy-tickets] [sqlalchemy] #2726: ColumnClause pretends to be iterable and then isn't

[sqlalchemy <mi...@zz...>]

#2726: ColumnClause pretends to be iterable and then isn't
-----------------------------------+-----------------------------------
      Reporter:  gthb              |      Owner:  zzzeek
          Type:  defect            |     Status:  closed
      Priority:  high              |  Milestone:  0.8.xx
     Component:  sql               |   Severity:  trivial - <10 minutes
    Resolution:  fixed             |   Keywords:
Progress State:  completed/closed  |
-----------------------------------+-----------------------------------
Changes (by zzzeek):

 * status:  new => closed
 * resolution:   => fixed
 * status_field:  in queue => completed/closed


Comment:

 r33d3e11cbf596c64abc3ced1d5aa01989afe0ad8 0.8

 rd993bdeac8674db88e 0.9

--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/2726#comment:4>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python